11.3 One-variable Data Table
In short: A Data Table shows the result for many values of one input at once (sensitivity 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
Swapping the Row input cell and Column input cell in a Data Table is a classic mistake of many students, and then all the results come out the same. If the values are written downwards (in a column), use the Column input cell. And the input cell must be on the same sheet as the Data Table. A big data table makes the file slow – you can use Formulas › Calculation Options › Automatic Except for Data Tables.
Ravindra Bagale's Tip – मराठी
Data Table मध्ये Row input cell आणि Column input cell उलटे देणं – ही बऱ्याच students ची classic चूक आहे, आणि सगळे results same येतात. Values खाली (column मध्ये) लिहिल्या असतील तर Column input cell. आणि input cell Data Table च्या sheet वरच असायला हवी. मोठा data table file slow करतो – Formulas › Calculation Options › Automatic Except for Data Tables वापरू शकता.
Ravindra Bagale's Tip – हिंदी
Data Table में Row input cell और Column input cell उल्टे दे देना – यह बहुत से students की classic गलती है, और सारे results same आते हैं. Values नीचे की ओर (column में) लिखी हों तो Column input cell. और input cell Data Table वाली sheet पर ही होनी चाहिए. बड़ी data table file को slow कर देती है – Formulas › Calculation Options › Automatic Except for Data Tables इस्तेमाल कर सकते हो.
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.