Microsoft Office Tutorials and References

In Depth Information

**Chapter 18: Performing a what-if analysis**

In the Data Table dialog box, shown in Figure 18-2, specify the location of the input cell in

the Row Input Cell or Column Input Cell box. The
input cell
is the placeholder cell referred

to by the table formula—in this example, A2. If the input values are arranged in a row,

type the input cell reference in the Row Input Cell box. If the values in the input range are

arranged in a column, as in our example, use the Column Input Cell box.

Figure 18-2
Use the Data Table dialog box to specify the input cell.

After you click OK, Excel enters the results of the table formula (one result for each input

value) in the available cells of the data table range. In this example, Excel enters six results

in the range C3:C9, as shown in Figure 18-3, with a little formatting we added for easier

reading.

Figure 18-3
The monthly loan payments for each interest rate now appear in the data table, as

shown on the Data Table2 tab of the Goal Seek.xlsx workbook.

When you create this data table, Excel enters the array formula { =TABLE(,A2)} in each

cell in the
results range
C3:C9. In the sample data table, the formula computes the results

of the PMT function by using each of the interest rates in column B. After you build the

table, you can change the loan amount or any of the interest rate values to see the results

immediately.

Note

The TABLE function is an internal function, meaning that you can’t select it in the Insert

Function dialog box or type it manually.