Page 84 - Excel Data Analysis

05 537547 Ch04.qxd 3/4/03 11:52 AM Page 70

EXCEL DATA ANALYSIS

SOLVE A FORMULA WITH A DATA TABLE

f you want to compare how different values affect the When you create the formula, the argument you want to
results for an argument, you can do so by creating a substitute needs to reference the first value in the data
I data table. For example, an analyst can use this function table column. For example, if column A contains interest
to determine monthly payments on a loan at different rates to substitute into a PMT function, the Rate argument
interest rates. When you create a data table, you can use must reference the first cell in column A as shown in the
what-if comparisons to see the value of a formula when following sample formula:
arguments contain different values.
=PMT(A2/12, E3, -E2)
To use a data table to solve a formula, you must create a In the Table dialog box, you specify the row or column
table that contains at least two columns or two rows. If you containing the substitution values. You specify the
use columns, the first column contains the values you want substitution values by entering the first cell in the column
to substitute into an argument of the formula. The second or row. If your table contains the substitution values in a
column contains the formula in the first cell. If you use column, you place the first cell in the column in the Column
rows, you need to place the substitution values in the Input Cell field. If values are in a row, you place the first
first row.
value in the Row Input Cell field.

SOLVE A FORMULA WITH A DATA TABLE

⁄ Type the substitution ¤ Type the formula in the ‹ Select the cells containing Note: See Chapter 1 for more on
values in a column. first cell of the next column. substitution values and the selecting cells.
formula.
■ You can also place values ■ If your values are in a row, › Click Data ➪ Table.
in a row. type the formula in the first
cell in the row under values.

70