Page 253 - Excel Workbook for Dummies

26_798452 ch18.qxp 3/13/06 7:45 PM Page 236
236 Part V: Doing Data Analysis
Creating two-variable data tables

In a two-variable data table, Excel substitutes a series of different values for two input
values in a formula. 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 formula in the
cell at the intersection of this row and column of input values.

Try It

Exercise 18-2: Constructing a Two-Variable Data Table in a Spreadsheet

Use the Solved18-1.xls workbook containing the one-variable data table on the
Data Table worksheet that you created in the last exercise to convert it into a two-
variable data table that not only uses a series of growth rate percentages but also a
series of projected expenses to sales percentages in calculating possible projected
incomes:

1. Delete the range C7:C17 on the Data Table worksheet.
Because this range contains the array formula with the TABLE function, you
must select the entire range, C7:C17, in order to be able to make any deletions.
2. Insert two new blank cells in the range A4:B4, shifting down the existing entries
(Projected Sales and the formula =Sales_2006+(Sales_2006*Growth_Rate) with
the calculated result $899,063) to the range A5:B5.
Be sure to select the Shift Cells Down option button in the Insert dialog box.
3. Make the following data entries in the designated cells:

• Expenses/Sales in cell A4
• 3% in cell B4
4. Assign the range name Expense_Rate to cell B4 containing the 3.00% rate of
expenses to sales.
5. Edit the formula in cell B5 as follows:
=Sales_2006+(Sales_2006*Growth_Rate)-(Sales_2006*
Expense_Rate)
6. Move the formula with the external reference to the Projected_Sales cell, B5, that
is currently in cell C6 to cell B7 of the Data Table worksheet.
When creating a two-variable data table, you place the formula at the inter-
section of the row and column in the table that contains the series of input
values.
7. Make the following data entries in the designated cells:

• 2% in cell C7
• 2.5% in cell D7
8. Format cell C7 with Percent Style number format with two decimal places and
then use the Fill handle to copy this series of half-percentage point increases
across row 7 to the cell range E7:G7 so that the series ends with a 4.00%
expense/sales growth rate.