11.3 One-variable Data Table
One-variable Data Table with example — in 30 seconds
What: A Data Table runs your model for a list of input values and shows every result at once.
Why: Instead of one break-even number, the manager sees profit for five order levels side by side.
Tiny example: At 1,000 orders profit is -₹9,000; at 2,000 orders it is ₹9,000.
One-variable Data Table with example — 30 सेकंदात
काय: Data Table तुमचं model input values च्या list साठी चालवतो आणि सगळे results एकदम दाखवतो.
का: एकाच break-even number ऐवजी manager ला पाच order levels चा profit शेजारी शेजारी दिसतो.
छोटं उदाहरण: 1,000 orders ला profit -₹9,000; 2,000 orders ला तो ₹9,000.
One-variable Data Table with example — 30 सेकंड में
क्या: Data Table आपके model को input values की list के लिए चलाता है और सारे results एक साथ दिखाता है.
क्यों: एक break-even number की जगह manager को पाँच order levels का profit साथ-साथ दिखता है.
छोटा उदाहरण: 1,000 orders पर profit -₹9,000; 2,000 orders पर यह ₹9,000 है.
First look at the data (before → after):
Expected result — Filled Data Table
| D: Orders | E: Profit (₹) |
|---|---|
| 1,000 | -9,000 |
| 1,250 | -4,500 |
| 1,500 | 0 |
| 1,750 | 4,500 |
| 2,000 | 9,000 |
About this data: Teaching sample for a fictional shop “GreenField Dairy”. Figures and names (e.g. Rohan Patil) are made up for this lesson on one variable data table.
Before table and formula as text
Before — Input list in D2:D6, formula =B4 in E1
| D: Orders | E: Profit |
|---|---|
| 1,000 | — |
| 1,250 | — |
| 1,500 | — |
| 1,750 | — |
| 2,000 | — |
About this data: Demo table only — not real customer or company data. Built to practise one variable data table.
Formula / click path
E1: =B4
Select D1:E6 › Data › What-If Analysis › Data Table…
Column input cell: B1
Do it step by step:
- Type the five order levels in D2:D6.
- In E1, type
=B4(the profit cell). - Select D1:E6.
- Click Data › What-If Analysis › Data Table….
- Leave Row input cell empty, set Column input cell to B1, and click OK.
स्टेप बाय स्टेप करून बघा:
- D2:D6 मध्ये पाच order levels type करा.
- E1 मध्ये
=B4type करा (profit cell). - D1:E6 select करा.
- Data › What-If Analysis › Data Table… वर click करा.
- Row input cell रिकामा ठेवा, Column input cell मध्ये B1 टाका, आणि OK करा.
स्टेप बाय स्टेप करके देखें:
- D2:D6 में पाँच order levels type करें.
- E1 में
=B4type करें (profit cell). - D1:E6 select करें.
- Data › What-If Analysis › Data Table… पर click करें.
- Row input cell खाली रखें, Column input cell में B1 डालें, और OK करें.
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 |
About this data: Made-up rows so you can copy the pattern for one variable data table. Currencies may use ₹, £, € or $.
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.
Loading practice tasks...
- Revenue at £18 (Easy): Revenue in row 2.
- Revenue at £28 (Easy): Revenue in row 7.
- Profit at £22 (Easy): Profit in row 4 using cost B8.
- All revenues (spill) (Medium): Revenue for rows 2 to 7 in one formula.
- All profits (spill) (Medium): Profit for rows 2 to 7 using cost B8.
- Highest revenue (Medium): Biggest revenue across rows 2 to 7.
- Highest profit (Medium): Biggest profit across rows 2 to 7.
- Best price for profit (Hard): The price in A2:A7 that gives the highest profit.
- Units lost from £18 to £28 (Hard): Units at £18 minus units at £28.
- Prices with profit above £14,000 (Hard): How many prices give profit above 14000?
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
