Excel 2000 in a Nutshell

Name

Data → Table

Synopsis

If you find that you need to try different values within a formula to achieve the desired results, you should consider using Data → Table. The Data → Table option allows you to use what-if comparisons to determine the values of formulas if cells contain different values. For example, if you are trying to determine what your payment will be on a loan at various interest rates, you could either manually modify the function, or you could use the Data → Table option.

When you use the Data → Table option, you create a table that contains the values you want to compare and the results of the comparisons, as shown in Figure 9-14. In the figure, column B contains a list of the possible interest rates for the loan. When you select Data → Table, Excel will use the formula in cell C3 to calculate the payment for the loan based upon the various interest rates and place the payment amount in the cell next to the interest rate. The cells in column F are used to calculate the formula in C3, =PMT(F3/12,F4,-F5).

Before selecting the Data → Table option, you need to select the data table, as shown in Figure 9-14. The data table is essentially the column that contains the values that will be substituted in the formula and the column where the results will be placed. Keep in mind you can also use rows for this.

Figure 9-14. Create a data input table for your formula

After ...