• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 14. Using Excel's Business-Model... > Setting Up a One-Input Data Table

Setting Up a One-Input Data Table

The problem with modifying formula variables is that you see only a single result at one time. If you're interested in studying the effect a range of values has on the formula, you need to set up a data table. In the investment analysis worksheet, for example, suppose that you want to see the future value of the investment with the annual deposit varying between $7,000 and $13,000. You could just enter these values in a row or column and then create the appropriate formulas. Setting up a data table, however, is much easier, as the following procedure shows:

1.
Add to the worksheet the values you want to input into the formula. You have two choices for the placement of these values:

  • If you want to enter the values in a row, start the row one cell up and one cell to the right of the formula.

  • If you want to enter the values in a column, start the column one cell down and one cell to the left of the cell containing the formula, as shown in Figure 14.2.

    Figure 14.2. Enter the values you want to input into the formula.

2.
Select the range that includes the input values and the formula. (In Figure 14.2, this is B9:C16.)

3.
Choose Data, Table. Excel displays the Table dialog box.

4.
How you fill in this dialog box depends on how you set up your data table:

  • If you entered the input values in a row, use the Row Input Cell text box to enter the cell address of the input cell.

  • If the input values are in a column, enter the input cell's address in the Column Input Cell text box. In the investment analysis example, you enter C4 in the Column Input Cell, as shown in Figure 14.3.

    Figure 14.3. In the Table dialog box, enter the input cell where you want Excel to substitute the input values.

5.
Click OK. Excel places each of the input values in the input cell; Excel then displays the results in the data table, as shown in Figure 14.4.



Figure 14.4. Excel substitutes each input value into the input cell and displays the results in the data table.



PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint