Microsoft Office Tutorials and References

In Depth Information

**Excel Compatibility Functions**

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

worksheets.

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.