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
Two-variable table madhe corner cell madhe formula (=B13) theva aani tyala custom format "Orders ↓ AOV →" sarkha dya – khup students formula cell rikami thevtat kiwa delete kartat aani table chalat nahi. Formula lapvaycha asel tar format ne text dakhva, delete karu naka.
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.