Creating a Megaformula: A Simple Example | Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

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.

image from book
Figure 20-1: This spreadsheet uses multiple formulas to calculate mortgage loan information.

On the CD

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
Open table as spreadsheet
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
C14	`=C13-C11`	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.

Note

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 intermediate formulas. C14 contains the following formula:

 =C13-C11

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

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 
```
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 
```
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) 
```
Copying Text from a Formula
Creating megaformulas involves copying formula text and then replacing a cell reference with the copied text. To copy the contents of a formula, activate the cell and press F2. Then select the formula text (without the equal sign) by pressing Shift+ Home, followed by Shift+→. Then press Ctrl+C to copy the selected text to the Clipboard. Press Esc to cancel cell editing. Then, activate the cell that contains the megaformula and press F2. Use the arrow keys, and hold down Shift to select the cell reference you want to replace. Finally, press Ctrl+V to replace the selected text with the clipboard contents.

In some cases, you need to insert parentheses around the copied formula text to make the formula calculate correctly. If the formula returns a different result after you paste the formula text, press Ctrl+Z to undo the paste. Insert parentheses around the formula you want to copy and paste it into the megaformula-it should then calculate correctly.
Substitute the formula contained in 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. You can safely delete the formulas in C10:C13. 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.