Microsoft Office Tutorials and References
In Depth Information
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.
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:
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.
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