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 |
About this data: Teaching sample for a fictional shop “Skyline Clinics”. Figures and names (e.g. Priya Deshmukh) are made up for this lesson on two variable data table.
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.
Loading practice tasks...
- Months (Easy): Months for the years in B5.
- €200k at 6% (Easy): Monthly payment for A2 at rate B2, years B5, as a positive number rounded to 2 decimals.
- €300k at 8% (Easy): Payment for A4 at rate D2, rounded to 2 decimals.
- €250k at 7% (Medium): Payment for A3 at rate C2, rounded to 2 decimals.
- Row of payments (spill) (Medium): Payments for A2 at the three rates B2:D2, rounded to 2 decimals.
- Column of payments (spill) (Medium): Payments for A2:A4 at rate B2, rounded to 2 decimals.
- Whole table (spill) (Hard): Payments for loans A2:A4 (down) and rates B2:D2 (across), rounded to 2 decimals.
- Cost of 1% more (Hard): For A3: payment at C2 minus payment at B2, rounded to 2 decimals.
- Total interest €300k at 8% (Hard): Payment for A4 at D2 × months − A4, rounded to 0 decimals.
- Cheapest payment over €1,500? (Hard): Is the payment for A3 at B2 above 1500? (TRUE or FALSE)
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
