11.4 Two-variable Data Table
Two inputs at once: one along the column, one along the row; the formula sits in the top-left corner.
Steps in Excel – orders per day × AOV
- In H2 type
=B13. - Orders per day down H3:H5: 400, 500, 600. AOV across I2:K2: 300, 320, 350.
- Select H2:K5 › Data › Forecast › What-If Analysis › Data Table…
- Row input cell:
B3(AOV, because AOV values are in the row) › Column input cell:B2(orders) › OK. - Apply a red–green colour scale (Module 2.7) to see the break-even frontier.
| Orders/day ↓ · AOV → | ₹300 | ₹320 | ₹350 |
|---|---|---|---|
| 400 | -₹1,38,000 | -₹94,800 | -₹30,000 |
| 500 | -₹60,000 | -₹6,000 | ₹75,000 |
| 600 | ₹18,000 | ₹82,800 | ₹1,80,000 |
Reading it: at ₹350 AOV the store is profitable from about 500 orders/day; at ₹300 it needs nearly 600.
Ravindra Bagale's Tip
In a two-variable table, keep the formula (=B13) in the corner cell and give it a custom format like "Orders ↓ AOV →" – many students leave the formula cell empty or delete it, and the table doesn't work. If you want to hide the formula, show text with a format; don't delete it.
Ravindra Bagale's Tip – मराठी
Two-variable table मध्ये corner cell मध्ये formula (=B13) ठेवा आणि त्याला "Orders ↓ AOV →" सारखा custom format द्या – बरेच students formula cell रिकामी ठेवतात किंवा delete करतात आणि table चालत नाही. Formula लपवायचा असेल तर format ने text दाखवा, delete करू नका.
Ravindra Bagale's Tip – हिंदी
Two-variable table में corner cell में formula (=B13) रखो और उसे "Orders ↓ AOV →" जैसा custom format दो – बहुत से students formula cell खाली छोड़ देते हैं या delete कर देते हैं और table नहीं चलती. Formula छुपाना हो तो format से text दिखाओ, delete मत करो.
Practice task
Build a two-variable table of Profit for gross margin % (15%, 18%, 21%) × orders per day (400, 450, 500, 550) and highlight profitable combinations.