Excel 2010 Bible

Chapter 17: Performing Magic with Array Formulas

393

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

The first method (column C) uses the 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 copied to the cells below it.

Note

Each ranking is computed with a separate array formula, not with an array formula entered into multiple cells. n

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

the number of equal values minus 1.

New Feature
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 17.8. This formula is in cell D4,
and copied to the cells below.

=RANK.AVG(B4,Sales)

FIGURE 17.8

Ranking data with the Excel’s RANK function and with array formulas.