Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

If you’ve used the Excel program’s RANK function, you may have noticed that the ranks

produced by this function don’t handle ties the way that you may like. For example, if two values are

tied for third place, the RANK function gives both of them a rank of 3. You may prefer a

commonly used approach that assigns each an average (or midpoint) of the ranks — in other words, a

rank of 3.5 for both values tied for third place.

Figure 15-8 shows a worksheet that uses two methods to rank a column of values (named Sales).

The first method (column C) uses the Excel RANK function. Column D uses array formulas to

compute the ranks.

The following is the array formula in cell D4:

{=SUM(1*(B4<=Sales))-(SUM(1*(B4=Sales))–1)/2}

This formula is then copied to the cells below it.

Each ranking is computed with a separate array formula, not with an array formula

entered into multiple cells.

Each array function works by computing the number of higher values and subtracting one half of

the number of equal values minus 1.

Excel 2010 includes a new worksheet function, RANK.AVG, that eliminates the need for

an array formula. The formula that follows returns the same rankings as shown in

Column D in Figure 15-8. This formula is in cell D4, and copied to the cells below.

=RANK.AVG(B4,Sales)

Figure 15-8:
Ranking data with Excel’s RANK function and with array formulas.