Microsoft Office Tutorials and References
In Depth Information
Chapter 21: Creating Mega-
In This Chapter
• What is a megaformula, and why would you want to use such a thing?
• How to create a megaformula
• Examples of megaformulas
• Pros and cons of using megaformulas
This chapter describes a useful technique that combines several formulas into a single formula — what I call a
“megaformula . ” This technique can eliminate intermediate formulas and may even speed up recalculation. The
downside, as you'll see, is that the resulting formula is virtually incomprehensible and may be impossible to edit.
What Is a Megaformula?
Often, a worksheet may require intermediate formulas to produce a desired result. In other words, a formula may
depend on other formulas, which in turn depend on other formulas. After you get all these formulas working cor-
rectly, you often can eliminate the intermediate formulas and create a single (and more complex) formula. For
lack of a better term, I call such a formula a “megaformula . ”
What are the advantages of employing megaformulas? They use fewer cells (less clutter), and recalculation may
be faster. And, you can impress people in the know with your formula-building abilities. The disadvantages? The
formula probably will be impossible to decipher or modify, even by the person who created it.
I use the techniques described in this chapter to create many of the complex formulas
presented elsewhere in this topic.
Using megaformulas is actually a rather controversial issue. Some claim that the clarity that results from having
multiple formulas far outweighs any advantages in having a single incomprehensible formula. You can decide for
Creating a Megaformula: A Simple Example