Excel 2003: The Missing Manual

Chapter 12. Advanced Formula Writing

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 that will help you get the most from your formulas.

In this chapter, you’ll tackle four new topics that deal with writing advanced formulas:

• 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 your worksheets are recalculated.

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

Conditions in Formulas

Chapter 7 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 only considered one conditional function—IF( )—which performs different actions depending on the result of a calculation.

For example, 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, for example, A20 contains the dollar amount of a sales invoice, you can use this formula ...