Microsoft Office Tutorials and References
In Depth Information
Excel Compatibility Functions
Sometimes your worksheet may have a list with unlimited growth potential, like a
list of expenses or a catalog of products. In this case, you can code your formulas
to include an entire column by leaving out the row number. For example, the range
A:A includes all the cells in column A (and, similarly, the range 2:2 includes all the
cells in row 2).
Excel Compatibility Functions
Some of Excel’s functions use extraordinarily complex logic behind the scenes. Over
the years, Excel experts have found minor flaws and quirks in some functions, like
cases where the functions deviate from mathematical standards.
Correcting these problems is a bit messy. If different versions of Excel use subtly
different calculation logic, you could find that your numbers change unpredictably—
for example, when you upgrade your software or when you pass your spreadsheet
to a colleague with a different version of Excel. In this case, consistency is more
important than absolute, theoretical accuracy.
To avoid this sort of situation, the designers of Excel rarely change an existing
function. Instead, in Excel 2010 they’ve added new functions that have similar names but
incorporate a period. For example, in Excel 2010 the RANK.AVG() and RANK.EQ()
functions replace the old-school RANK() function. Although RANK() still works,
Microsoft recommends you use one of its replacements in new worksheets. Because
RANK() is only kicking around to ensure that old worksheets keep working, it’s
called a compatibility function .
So how do you recognize compatibility functions, to make sure you don’t
accidentally use one when you actually want the more modern replacement? The trick is to
read the function tooltip, which clearly identifies compatibility functions, as shown
in Figure 17-6.
Figure 17-6:
The RANK()
function is included for
compatibility with old
Compatibility functions always
appear at the bottom
of the Formula
AutoComplete list,
and they’re further
distinguished by an
icon with a yellow
exclamation mark.
Search JabSto ::

Custom Search