Microsoft Office Tutorials and References
In Depth Information
Creating a Megaformula: A Simple Example
Creating a Megaformula: A Simple Example
Creating a megaformula basically involves copying formula text and pasting it into another
formula. I start with a relatively simple example. Examine the spreadsheet shown in Figure 20-1. This
sheet uses formulas to calculate mortgage loan information.
Figure 20-1: This spreadsheet uses multiple formulas to calculate mortgage loan information.
This workbook, named total interest.xlsx , is available on the companion
CD-ROM.
The Result Cells section of the worksheet uses information entered into the Input Cells section
and contains the formulas shown in Table 20-1.
Table 20-1: Formulas Used to Calculate Total Interest
Cell
Formula
What It Does
C10
=C4*C5
Calculates the down payment amount
C11
=C4–C10
Calculates the loan amount
C12
=PMT(C7/12,C6,–C11)
Calculates the monthly payment
C13
=C12*C6
Calculates the total payments
=C13–C11
C14
Calculates the total interest
Suppose you’re really interested in the total interest paid (cell C14). You could, of course, simply
hide the rows that contain the extraneous information. However, it’s also possible to create a
single formula that does the work of several intermediary formulas.
 
Search JabSto ::




Custom Search