Understand Excel 2010s new RANK.AVG() function

Excel 2010 has three RANK() functions, which might confuse your users. Learn the difference so you can help your users choose the right function for the job.

RANK() has been around for a long time, but with Excel 2010, you’ll find not one, but three ranking functions. RANK() and RANK.EQ() are the same but RANK.EQ() isn’t supported by versions earlier than 2010. That means you must use RANK() if sharing a workbook with users still depending on Excel 2007 and earlier. RANK.AVG() is also new and proves useful when dealing with repeated values.

All three functions have the same purpose - they return a value’s relative position within a list. For instance, in the following list of temperatures, RANK() tells us that 103 on July 6 has the highest rank. In this case, that also means 103 is the highest temperature. (If you use RANK()'s optional order argument, that won't be the case.) To learn more about RANK(), read How to use Microsoft Excel's RANK() function.

You might have noticed that RANK.EQ() repeats several values. When any of the ranking functions encounter repeated value, they return the same rank for each and skip the next ranking value in the sequence once for each repetition. This is easier to see if you can sort the ranked values. However, keep in mind that you won't always be able to do that. That's why it's important to understand this function's behavior.

The temperature 98 repeats once, generating a rank of 5 and skipping the rank value of 6. The temperature 95 occurs four times; all generate a rank of 9 and skip values 10 through 12. The next new temperature, 93, generates a rank of 13.

Most of the time, these skipped intervals won’t matter. When they do, an average rank might prove more useful - RANK.AVG() returns the average rank. This function still skips values, but differently. In the following sheet, compare the results of RANK.EQ() and RANK.AVG(). Instead of returning 5 for the temperature 98, RANK.AVG() returns 5.5, and it still skips 6.

Wait... we all know that the average of 5 and 5 is 5, not 5.6, so what's the function averaging? RANK.AVG() doesn't average the ranked results, but the ranked positions. In the case of the temperature 98, that would be 5 and 6; the average of 5 and 6 is 5.5.

Look at the temperature 95 - it repeats four times and RANK.AVG() returns 10.5, the average of 9, 10, 11, and 12. Look carefully, RANK.AVG() not only skips 11, and 12, it also skips 9. Unlike the RANK.EQ() and RANK() function, RANK.AVG() skips values before and after the repeating block.

Whether users stick with RANK.EQ() or use RANK.AVG() is up to your business rules. There's no right or wrong - just a new alternative.