Microsoft Office Tutorials and References
In Depth Information
To Create a Percent Series Using the Fill Handle
To Enter the Formulas in the Data Table
Formulas in Data
Any experienced Excel
user will tell you that to
enter the formulas at the
top of the data table,
you should enter the cell
reference or name of
the cell preceded by an
equal sign (Figure 4–24).
This ensures that if you
change the original
formula in the worksheet,
will change the
corresponding formula in
the data table. If you use
a cell reference, Excel also
copies the format to the
cell. If you use a name,
Excel does not copy the
format to the cell.
The next step in creating the data table is to enter the three formulas at the top of
the table in cells C9, D9, and E9. The three formulas are the same as the monthly payment
formula in cell E4, the total interest formula in cell E5, and the total cost formula in cell
E6. The number of formulas you place at the top of a one–input data table depends on the
application. Some one–input data tables will have only one formula, while others might have
several. In this case, three formulas are affected when the interest rate changes.
Excel provides four ways to enter these formulas in the data table: (1) retype the
formulas in cells C9, D9, and E9; (2) copy cells E4, E5, and E6 to cells C9, D9, and E9,
respectively; (3) enter the formulas =monthly_payment in cell C9, =total_interest in cell
D9, and =total_cost in cell E9; or (4) enter the formulas =e4 in cell C9, =e5 in cell D9, and
=e6 in cell E9.
The best alternative to deﬁ ne the formulas in the data table is the fourth one, which
involves using the cell references preceded by an equal sign. This is the best method
because: (1) it is easier to enter the cell references; (2) if you change any of the formulas
in the range E4:E6, the formulas at the top of the data table are updated automatically;
and (3) Excel automatically assigns the format of the cell reference (Currency style format)
to the cell. Using the names of the cells in formulas is nearly as good an alternative, but if
you use cell names, Excel will not assign the format to the cells. The following steps enter
the formulas of the data table in row 9.
1 With cell C9 active, type =e4 and then press the RIGHT ARROW key.
2 Type =e5 in cell D9 and then press the RIGHT ARROW key.
3 Type =e6 in cell E9 and then click the Enter box to complete the assignment of the
formulas and Currency style format in the range C9:E9 (Figure 4–24).
Why are these cells assigned the values of cells in the Loan Payment Calculator area of the
It is important to understand that the entries in the top row of the data table (row 9) refer
to the formulas that the loan department wants to evaluate using the series of percentages
in column B. Furthermore, recall that when you assign a formula to a cell, Excel applies the
format of the ﬁ rst cell reference in the formula to the cell. Thus, Excel applies the Currency
style format to cells C9, D9, and E9 because that is the format of cells E4, E5, and E6.
top row of
varying interest rates
are input values to be
substituted in cell E2