O'Reilly logo

Excel 2003: The Missing Manual by Matthew MacDonald

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 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 ...

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