One-variable data table is an excellent tool in excel to perform what-if analysis. It helps in getting output for series of input values for given formula. This avoids repeating the formula for each of the input value and above all it's dynamic to use.
The formulas in a one-variable data table refer to only one input cell and shows corresponding output. We can insert different input variables either in a row or a column. The ouput will be available in the next row or column as the case may be.
Also read: How to create a Two-Variable Data Table in Excel 2013
Let's straight away jump into an example to understand it better. The example given here is used to calculate the YoY population growth of country X. For example, the population in 2012 is 5,955 million (cell B2), the growth rate is inserted in cell B3 (say 3.25%).
Also read: How to create a Two-Variable Data Table in Excel 2013
Let's straight away jump into an example to understand it better. The example given here is used to calculate the YoY population growth of country X. For example, the population in 2012 is 5,955 million (cell B2), the growth rate is inserted in cell B3 (say 3.25%).
Output calculated as: [Population_2012]+[Growth_Rate*Population_2012] OR = B2+(B3*B2)
The final output derived for this is in cell B5 (6,149 million in 2013).
Now, the one-variable data table is useful when you want resultant population in 2013 for different growth rates (what-if analysis). Let's insert the range of growth rates ranging 1% to 10% in column C in the cell range C10:C19 (same could be done for a row).
Following steps will be useful to create one-variable data table that will give population in 2013 for different inputs of growth rates:
1. Copy the formula for calculation (originally in cell B5) into cell C9 (on the top of output column) by typing =(equal to) and then clicking cell B5 (to create formula reference for your output)
This enables the table function to understand what formula to apply on the input cell in order to get the output.
2. Select data range B9:C19.
This range includes input variables, the formula linked in step-1 and range desired for output variables.
3. Go to Ribbon. Click...Data--->What-if Analysis--->Data Table [ Shortcut: Alt+AWT ]
This will show a Data Table dialog box on the Excel screen.
4. Click on the Column Input Cell text box in the dialog box (Row Input Cell text box in case output is needed in row) and then select cell B3 by clicking it (the cell with original formula)
This will insert absolute cell reference $B$3 of the formula into the Column Iput Cell text box of the dialog box
5. Click Ok.
On clicking "OK", the dialog box will get closed. On minute look into the formula bar, the cell range C9:C19 will show TABLE function.
6. Copy format to the ouput range by Format Painter
Alternatively, copy from original output (cell B5) and paste format into output range[Shortcut: Alt+EST]
IMPORTANT NOTES:
- The output cell range (here C9:C19) will show TABLE function with formula cell (B3) inside {} brackets. This means it is an array. Hence, you cannot delete part of this range (will throw error while doing so). To delete or clear the content it is necessary to select entire range.
- The input range is dynamic. If you change the input variables (B9:B19), it will automatically change the output variable.
- If the excel is in automatic mode, all the data table gets updated each time you refresh or save the excel workbook (and this may be irritating sometimes..!!). In order to prevent Excel from updating these data table until we Refresh (F9) or Calculate sheet (Shift+F9) on the Formulas tab,
Go to File--->Options--->Formulas--->Workbook Calculation--->Automatic except for Data Tables
[Shortcut: Alt+MXE]
[Shortcut: Alt+MXE]
OR Go to Formulas tab--->Calculation Options--->Automatic except for Data Tables
More interesting articles on this website:
No comments:
Post a Comment