I have just recently (a week ago) started learning Excel, and I'm struggling to understand why Rank.avg() and Rank.eq() are giving duplicate rankings, when all the values are different. Neither my teacher nor anyone else I've asked so far have been able to explain this (they just dismiss it), hence why I am asking here.
A solution would also be helpful, but I'm currently more interested in learning about why this is happening. I have a little bit of python knowledge, if that is helpful?
Presuming you are using the rank.avg and rank.eq formulas to find the rank of the field called "avg", then this is the expected behavior.
From Microsoft's support site for RANK.EQ:
RANK.EQ gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).
From Microsoft's support site for RANK.AVG:
Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned.
Those two formula only return different results if there are duplicates in the data and the results will only be different for those duplicate numbers. If a number appears more than once, RANK.EQ returns the rank of first occurrence and RANK.AVG will return the average of all its ranks. Here is an example with 10 random values that I then sorted in ascending order. Notice the results for 8 and 10 are different based on which function is used.
| Value | RANK.EQ | RANK.AVG |
|---|---|---|
| 1 | 10 | 10 |
| 3 | 9 | 9 |
| 5 | 8 | 8 |
| 6 | 7 | 7 |
| 7 | 6 | 6 |
| 8 | 3 (highest rank is 3) | 4 (average of 3, 4, & 5) |
| 8 | 3 | 4 |
| 8 | 3 | 4 |
| 10 | 1 (highest rank is 1) | 1.5 (average of 1 & 2) |
| 10 | 1 | 1.5 |
