Software

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
jody.burton
jody.burton

...it seems the larger the groupings within the list, the more representative the returned ranking would seem with RANK.AVG. Imagine a group of 10 kids taking a test (pardon the cliched scenario.) One kid scores 100, another scores 98, and the other eight score 99. With RANK.EQ, the eight scoring 99 would all be ranked 2nd, while the one scoring 98 would be ranked 10th. Using RANK.AVG, the only change would be the eight scoring 99 would be ranked 5th, which is representative of middle of the pack performance.

Mark W. Kaelin
Mark W. Kaelin

Have you used RANK.AVG before? Can you think of a good use for the function?