O'Reilly logo

Excel 2016 Formulas by Richard Kusleika, Michael Alexander

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 21 Creating Megaformulas

In This Chapter

  • What a megaformula is and why you would want to use such a thing
  • How to create a megaformula
  • Examples of megaformulas
  • How to use named formulas to create a megaformula
  • Pros and cons of using megaformulas

This chapter describes a useful technique that combines several formulas into a single formula—what we 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 correctly, you often can eliminate the intermediate formulas and create a single (and more complex) formula. For lack of a better term, we 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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required