Microsoft Office Tutorials and References

In Depth Information

**Formula Errors**

Formula Errors

Unfortunately, the case for ditching compatibility functions isn’t as clear-cut as it

seems. The problem is that the new functions won’t work in older versions of Excel.

For example, imagine you use a function like RANK.EQ() and send your

spreadsheet to a colleague who’s using Excel 2007. Because Excel doesn’t know anything

about this function, it can’t evaluate the formula. Instead, it shows the infamous

#NAME? error (page 476) in the cell.

So the bottom line is this: for now, it’s safer to keep using compatibility functions instead

of their newer replacements. But if a replacement has the behavior you really want, and

you don’t expect to share your work with other people, you can use the new functions.

Note:
Almost all of the functions you’ll learn about in this topic are traditional functions that have been

with Excel for generations. You’ll get a clear warning when we discuss new Excel 2010 functions (those

with a period in their names), so you don’t use them unknowingly.

Formula Errors

If you make a syntax mistake when entering a formula (like leaving out a function

argument or including a mismatched number of parentheses), Excel lets you know

right away. Moreover, like a stubborn schoolteacher, Excel won’t accept the formula

until you’ve corrected it. It’s also possible, though, to write a perfectly legitimate

formula that doesn’t return a valid answer. Here’s an example:

=A1/A2

If both A1 and A2 have numbers, this formula works without a hitch. However, if

you leave A2 blank, or if you enter text instead of numbers, then Excel can’t evaluate

the formula, and it reminds you with an error message.

Excel lets you know about formula errors by using an
error code
that begins with the

number sign (#) and ends with an exclamation point (!), as shown in Figure 17-7. In

order to remove this error, you need to track down the problem and resolve it, which

may mean correcting the formula or changing the cells it references.

Figure 17-7:

When Excel spots an error, it

inserts a tiny green triangle into

the cell’s top-left corner. When

you move to the offending cell,

Excel displays an exclamation

mark icon next to it (a smart

tag). Hover over the

exclamation mark to view a description

of the error (which appears in

a tooltip), or click the

exclamation icon to see a list of menu

options.