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.

Search JabSto ::

Custom Search