11.3 One-variable Data Table
A Data Table shows the result for many values of one input at once (sensitivity table).
Steps in Excel – profit for different orders per day
- In D3:D7 type the input values: 350, 400, 450, 500, 550.
- In E2 (one row above and one column right of the first value) type
=B13(link to Profit). - Select D2:E7.
- Data › Forecast › What-If Analysis › Data Table… › leave Row input cell empty › Column input cell:
B2› OK.
| Orders/day | Profit |
|---|---|
| 350 | -₹1,39,200 |
| 400 | -₹94,800 |
| 450 | -₹50,400 |
| 500 | -₹6,000 |
| 550 | ₹38,400 |
The cells contain {=TABLE(,B2)} – you cannot edit a single cell of the result; select the whole result area to delete it.
Ravindra Bagale's Tip
Data Table madhe Row input cell aani Column input cell ulte dene – hi khup students chi classic chuk aahe, aani sagle results same yetat. Values khali (column madhe) lihilya astil tar Column input cell. Ani input cell Data Table chya sheet varach asayla havi. Motha data table file slow karto – Formulas › Calculation Options › Automatic Except for Data Tables vapru shakta.
Practice task
Build a one-variable data table of Profit for delivery costs ₹20 to ₹40 in steps of ₹4. Add a second result column for Revenue.