Page 251 - Excel Workbook for Dummies

26_798452 ch18.qxp 3/13/06 7:45 PM Page 234
234 Part V: Doing Data Analysis

Try It

Exercise 18-1: Constructing a One-Variable Data Table in a Spreadsheet

If Excel is not currently running, launch the program and then use Sheet1 of the new
blank workbook to practice creating a single-variable data table that computes pro-
jected sales growth for 2007 based on various growth rate percentages:
1. Enter the title, 2007 Sales Projections, in cell A1, make it bold italic, and then
widen column A to display this entire title.
2. Make the following data entries in the designated cells:
• Sales 2006 in cell A2
• Growth Rate in cell A3
• Projected Sales in cell A4
• $875,000 in cell B2
• 2.75% in cell B3
3. Name the cells in the cell range B2:B4 with the headings you entered in the cell
range A2:A4.
Select the entire range A2:B4, open the Create Names dialog box (Insert➪Name➪
Create), and then be sure that the only the Left Column check box is selected
before you select OK.
4. Position the cell cursor in the Projected_Sales cell, B4, and construct the follow-
ing formula:
=Sales_2006+(Sales_2006*Growth_Rate)
5. Format the cell with the Currency number format with zero (0) decimal places.
6. In cell C6, create a formula with an external reference to the Projected_Sales cell,
B4, that brings its value forward to the cell and then format the result with the
Currency number format with zero (0) decimal places.
7. Enter the following potential growth rate percentages in the designated cells:
• 1.0% in cell B7
• 1.5% in cell B8
8. Use the Fill handle to copy this series of half-percentage point increases
down column B to the cell range B7:B17 so that the series ends with a 6.00%
growth rate.
Be sure to select cell B7 and B8 as a range with the cell cursor before you drag
the Fill handle down to cell B17.
9. Select the cell range B6:C17 and then choose Data➪Table to open the Table
dialog box.
This Table dialog box contains two text boxes: Row Input Cell, where you indi-
cate the variable from the top row of the data table that is to be substituted in
the master formula, and Column Input Cell, where you indicate the variable that
is to be substituted from its first column.
Because this one-variable input table contains the substitute values in a single
column of the data table (cell range B7:B17), you only need to use the Column
Input Cell text box in this case. And because the Growth_Rate cell, B3, contains