Microsoft Office Tutorials and References

In Depth Information

**Creating a Megaformula: A Simple Example**

This example is for illustration only. The CUMIPMT function provides a more direct way

to calculate total interest on a loan.

The formula that calculates total interest depends on the formulas in cells C11 and C13 (which are

the direct precedent cells). In addition, the formula in cell C13 depends on the formula in cell C12.

And cell C12, in turn, depends on cell C11. Therefore, calculating the total interest uses five

formulas. The steps that follow describe how to create a single formula to calculate total interest so

that you can eliminate the four intermediate formulas. C14 contains the following formula:

=C13–C11

The steps that follow describe how to convert this formula into a megaformula:

1.
Substitute the formula contained in cell C13 for the reference to cell C13.

Before doing this, add parentheses around the formula in C13. (Without the parentheses,

the calculations occur in the wrong order.) Now the formula in C14 is

=(C12*C6)–C11

2.
Substitute the formula contained in cell C12 for the reference to cell C12. Now the formula

in C14 is

=(PMT(C7/12,C6,–C11)*C6)–C11

3.
Substitute the formula contained in cell C11 for the two references to cell C11. Before

copying the formula, you need to insert parentheses around it. Now the formula in C14 is

=(PMT(C7/12,C6,–(C4–C10))*C6)–(C4–C10)

4.
Substitute the formula contained in cell C10 for the two references to cell C10. Before

copying the formula, insert parentheses around it. After you’ve done so, the formula in

C14 is

=(PMT(C7/12,C6,–(C4–(C4*C5)))*C6)–(C4–(C4*C5))

At this point, the formula contains references only to input cells. The formulas in range C10:C13

are not referenced, so you can delete them. The single megaformula now does the work

previously performed by the intermediary formulas.

Unless you’re a world-class Excel formula wizard, it’s quite unlikely that you could arrive at that

formula without first creating intermediate formulas.

Creating a megaformula essentially involves substituting formula text for cell references in a

formula. You perform substitutions until the megaformula contains no references to formula cells. At

each step along the way, you can check your work by ensuring that the formula continues to

display the same result. In the previous example, a few of the steps required parentheses around the

copied formula in order to ensure the correct order of calculation.