Page 667 - Excel 2013 All-in-One For Dummies

Using Data Tables 649
If you want to see how using a different range of variables affects the results in the table, you only need to enter the new input values in the existing range. By default, Excel automatically recalculates the results in the output range of a data table whenever you change any of its input values. If you want to control when each data table is recalculated while still allowing the formulas in the worksheet to be automatically recalculated, click the Automatic Except Data Tables option on the Options command button on the Formulas tab (Alt+MXE).
Excel computes the results in a data table by creating an array formula that uses the TABLE function. (See Book III, Chapter 1, for more information on array formulas.) In this example, the array formula entered into the cell range C8:C17 is as follows:
{=TABLE(,B3)}
The TABLE function can take two arguments, row_ref and/or column_ref, which represent the row input cell and column input cell for the data table, respectively. In this example, the data table uses only a column input cell,
so B3 is the second and only argument of the TABLE function. Because Excel enters the results in a data table by using an array formula, Excel won’t allow you to clear individual result cells in its output range. If you try to delete a single result in the data table, Excel displays an Alert dialog box, stating that you can’t change part of a table.
If you want to delete just the results in the output range of a data table, you must select all the cells in the output range (cell range C8:C17, in the current example) before you press the Delete key or choose the Clear All option from the Clear button’s drop-down menu (or press Alt+HEA).
Creating a two-variable data table
When you have a master formula in a worksheet in which you want to see the effect of changing two of its input values, you create a two-variable data table. When you create a two-variable data table, you enter two ranges of input values to be substituted in the master formula: a single-row range in the first row of the table and a single-column range in the first column of the data table. When you create a two-variable data table, you place a copy of the master for- mula in the cell at the intersection of this row and column of input values.
Figure 1-3 shows the typical setup for a two-variable data table. This figure uses the projected sales worksheet shown previously in the section on a one-variable data table. Here, however, a second variable has been added to projecting the total sales in 2014. This worksheet contains a value in cell B4 (named Expenses_2014) that shows the projected percentage of expenses to sales, which is used, in turn, in the master formula in cell B5 as follows:
=Sales_2013+(Sales_2013*Growth_2014)- (Sales_2013*Expenses_2014)
Book VII Chapter 1
Performing What-If Scenarios