Problem: The RANK function behaves strangely when there are ties. It is possible to have a list where two people are ranked second and no one is ranked third. In Fig. 383, Dora and Jerry are ranked second with 90 units produced. Next is Harry with 86 units. Harry will receive a rank of 4.
slide 1 of 2
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:
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.