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