Microsoft Office Tutorials and References
In Depth Information
Playing What-If with Data Tables
Array formulas and the TABLE function in data tables
Excel’s Data Table feature works by creating a
special kind of formula called an array formula
in the blank cells of the table. An array formula
(enclosed in a pair of curly brackets) is unique
in that Excel creates copies of the formula into
each blank cell of the selection at the time you
enter the original formula. (You don’t make
the formula copies yourself.) As a result,
editing changes, such as moving or deleting, are
restricted to the entire cell range containing the
array formula — it’s always all or nothing when
editing these babies!
Creating a two-variable data table
To create a two-variable data table, you enter two ranges of possible input
values for the same formula in the Data Table dialog box: a range of values
for the Row Input Cell across the first row of the table and a range of values
for the Column Input Cell down the first column of the table. You then enter
the formula (or a copy of it) in the cell located at the intersection of this row
and column of input values.
Figure 8-3 illustrates this type of situation. This version of the projected sales
spreadsheet uses two variables to calculate the projected sales for year 2014:
a growth rate as a percentage of increase over last year’s sales (in cell B3
named Growth_2014) and expenses calculated as a percentage of last year’s
sales (in cell B4 named Expenses_2014). In this example, the original formula
created in cell B5 is a bit more complex:
=Sales_2013+(Sales_2013*Growth_2014) -
(Sales_2013*Expenses_2014)
To set up the two-variable data table, I added a row of possible Expenses_2014
percentages in the range C7:F7 to a column of possible Growth_2014
percentages in the range B8:B17. I then copied the original formula named Projected_
Sales_2014 from cell B5 to cell B7, the cell at the intersection of this row of
Expenses_2014 percentages and column of Growth_2014 percentages with the
formula:
=Projected_Sales_2014                 Search JabSto ::

Custom Search