Chapter 13. Advanced Formula Writing and Troubleshooting

Over the last five chapters, you’ve learned how to use Excel’s impressive function toolkit to calculate everything from statistical trend lines to a payment schedule for a home mortgage. Now that you’ve had a close look at these functions, it’s time to consider a few techniques to help you get the most from your formulas.

In this chapter, you’ll tackle four new topics that’ll make you a formula master:

  • How to use conditional logic with functions like SUMIF() and COUNTIF().

  • How to make formulas more readable by using named ranges.

  • How to control when Excel recalculates your worksheets.

  • How to solve mysterious errors by using Excel’s formula auditing tools.

Conditions in Formulas

Chapter 8 gave you a first look at how to use conditional logic when writing Excel formulas. The basic principles are easy: you construct a condition using the logical operators like <, >, =, and <>, and then use this condition with a conditional function. So far, you’ve considered only one conditional function—IF()—which performs different actions depending on the result of a calculation.

Here’s an example that uses the IF() function. The following formula carries out the operation in either the second or third argument, depending on the value of cell A20:

=IF(A20>10000, A20*5%, A20*3%)

Translation: For values greater than 10,000, Excel executes the formula A20*5%; otherwise, it carries out the second formula. If A20 contains the dollar amount of a sales invoice, ...

Get Excel 2010: The Missing Manual now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.