Part IV: Managing and Analyzing Data
FIGURE 31.1
This simple worksheet model uses four input cells to produce the results.This workbook is available on this book’s website at http://www.wiley.com/go/excel2019bible. The
filename is mortgage loan.xlsx.With this worksheet, you can easily answer the following what-if questions:■ (^) What if I can negotiate a lower purchase price on the property?
■ (^) What if the lender requires a 20 percent down payment?
■ (^) What if I can get a 40-year mortgage?
■ (^) What if the interest rate increases to 5.50 percent?
You can answer these questions by simply changing the values in the cells in range C4:C7
and observing the effects in the dependent cells (C10:C13). You can, of course, vary any
number of input cells simultaneously.
Avoid Hard-Coding Values in a Formula
The mortgage calculation example, simple as it is, demonstrates an important point about spread-
sheet design: You should always set up your worksheet so that you have maximum flexibility to make
changes. Perhaps the most fundamental rule of spreadsheet design is the following:
Do not hard-code values in a formula. Instead, store the values in separate cells and use cell
references in the formula.
The term hard-code refers to the use of actual values, or constants, in a formula. In the mortgage loan
example, all of the formulas use references to cells, not actual values.