• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 5. Troubleshooting Formulas > Erroneous Formula Results

Erroneous Formula Results

If a formula produces no warnings or error values, the result might still be in error. If the result of a formula is incorrect, here are a few techniques that can help you understand and fix the problem:

  • Calculate complex formulas one term at a time— In the formula bar, highlight the term you want to calculate, and then press F9. Excel converts the highlighted section into its value. Make sure that you press the Esc key when you're done, to avoid entering the formula with just the calculated values.

  • Evaluate the formula— This feature enables you to step through the various parts of a formula. See “Evaluating Formulas,” later in this chapter.

  • Break up long or complex formulas— One of the most complicated aspects of formula troubleshooting is making sense out of long formulas. The previous techniques can help (by enabling you to evaluate parts of the formula), but it's usually best to keep your formulas as short as you can at first. When you get things working properly, you often can recombine formulas for a more efficient model.

  • Recalculate all formulas— A particular formula might display the wrong result because other formulas on which it depends need to be recalculated. This is particularly true if one or more of those formulas uses custom VBA functions. Press Ctrl+Alt+F9 to recalculate all worksheet formulas.

  • Pay attention to operator precedence— As explained in Chapter 3, “Building Basic Formulas,” Excel's operator precedence means that certain operations are performed before others. An erroneous formula result could therefore be caused by Excel's precedence order. To control precedence, use parentheses.

  • Watch out for nonblank “blank” cells— A cell might appear to be blank, but it might actually contain data or even a formula. For example, some users “clear” a cell by pressing the spacebar, which Excel then treats as a nonblank cell. Similarly, some formulas return the empty string instead of a value (see, for example, the IF() function formula I showed you earlier in this chapter for avoiding the #DIV/0! error).

  • Watch unseen values— For a large model, your formula could be using cells that you can't see because they're offscreen or on another sheet. Excel's Watch Window enables you to keep an eye on the current value of one or more cells. See “Watching Cell Values,” later in this chapter.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint