Microsoft Excel Help: Rank A List Without Ties

Microsoft Excel Help: Rank A List Without Ties
Page content

This behavior is by design. However, if you are later going to use VLOOKUPs to sort the employees by productivity, having two people ranked as #2 and no one ranked as #3 is not a good situation to be in.

In the formulas in columns F and G, the spreadsheet designer counted on there being one employee at each rank from 1 to 10. Since Excel did not assign anyone to a rank of #3 or #7, Jerry and Bill do not show up in the list, as shown in Fig. 384.

Strategy: In this case, you absolutely want the list in A4:A13 to be ranked without ties. The generally accepted solution may seem rather convoluted, but it works. In plain language, the formula in column C will say, “Give me the RANK of this value, plus 1 for every row above me that has an identical score.” As shown in Fig. 385, this can be accomplished with the following formula:

=RANK(B4,$B$4:$B$13)+COUNTIF(B$3:B3,B4)

As you copy this formula down, the first parameter of COUNTIF will expand to include B3 down to the row above the current row. Thus, in cell C13, the formula will be as follows: =RANK(B13,$B$4:$B$13)+COUNTIF(B$4:B12,B13)

The COUNTIF portion of the formula counts how many rows above the current row have an identical score. For each row above that is a tie, 1 gets added to the current row. This causes Bill to be ranked seventh instead of sixth. It may not be fair that Ashley appears before Bill, but in the summary report, anyone can notice that they have a tie.

Summary: Add a COUNTIF function to the RANK function in order to prevent ties.

Functions Discussed: =RANK(); =COUNTIF()

See all Microsoft Excel tips

Images

Fig. 384

Fig. 385