Microsoft Office Tutorials and References
In Depth Information
Playing What-If with Data Tables
Figure 8-1:
Sales
projection
spreadsheet with
a column
of possible
growth
percentages to
plug into a
one-variable
data table.
As you can see in Figure 8-2, I entered a column of possible growth rates
ranging from 1% all the way to 5.5% down column B in the range B8:B17.
To create the one-variable data table shown in Figure 8-2 that plugs each of
these values into the sales growth formula, I follow these simple steps:
1. Copy the original formula entered in cell B5 into cell C7 by
typing (equal to) and then clicking cell B5 to create the formula =
=Projected_Sales_2014.
The copy of the original formula (to substitute the series of different
growth rates in B8:B17 into) is now the column heading for the one-vari-
able data table.
2. Select the cell range B7:C17.
The range of the data table includes the formula along with the various
growth rates.
3. Click Data What-If Analysis Data Table on the Ribbon.
Excel opens the Data Table dialog box.
4. Click the Column Input Cell text box in the Data Table dialog box and
then click cell B3, the Growth_2014 cell with the original percentage,
in the worksheet.
Excel inserts the absolute cell address, $B$3, into the Column Input Cell
text box.
Search JabSto ::




Custom Search