From the Not Just Numbers blog:
Before I start, a couple of announcements.
First of all, please accept my apologies for the time between posts. A combination of workload and a family bereavement have slowed me down somewhat!
Secondly, if any of you will be in and around London tomorrow (Wednesday) night, the ICAEW are launching their Spreadsheet Competency Framework, with some top speakers from the world of Excel. This document (as suggested by its name) is intended to provide a framework to assess spreadsheet competency, and was developed by the Institute’s Excel Community Advisory Committee, of which I am a member. The event is free to attend, and further details can be found at:
http://www.icaew.com/en/technical/information-technology/it-faculty/it-faculty-events
I hope to see you there.
Right…on with the post.
There are many reasons, you might want to rank a list of numbers in Excel. One I do quite often, is produce a league table. By calculating the ranking of a number and then using lookups to populate a table in rank order, we can easily produce a league table, like the example below:
The first Rank column is the one we are trying to populate here.
In Excel 2010 onwards we will use the RANK.EQ function, which replaces the RANK function in Excel 2007 and before. Both functions work exactly the same, and both exist in Excel 2010 to 2016, at least. If you know you may have users using Excel 2007 or earlier, use the RANK function – otherwise use RANK.EQ to future-proof your spreadsheet.
The RANK.EQ (or indeed the RANK) function’s syntax is as follows:
=RANK.EQ(number,ref,[order])
where:
number is the number we want to know the rank of
ref is the whole range of numbers
order is an optional argument, which determines the order that the numbers should be ranked in. If this argument is zero, or omitted, the numbers are ranked in descending order (the largest number is ranked 1), whereas if this is 1 (or any non-zero value), the numbers are ranked in ascending order.
Where two numbers in the range are the same, they are both given the highest rank (when using the RANK.EQ function – there is a similar RANK.AVG function that gives them their average rank).
In our example the numbers 30,45,97, etc. are in the range B3:B12, so we would use the RANK.EQ function in cells C3:C12. In cell C3, we would enter:
=RANK.EQ($B3,$B$3:$B$12)
Notice that we have fixed all of the references (using the dollar signs) on the B3:B12 range, to ensure that this range stays fixed when we copy it down, but just the column on the B3 reference so that the row (and the number we rank) changes as we copy down.
If we use this function as it is, we will run into a problem, though. As there are two number 24s in our range, we get the following:
Notice that both 24s are ranked 8 (the highest rank), which means there is no rank 9 – hence our error in the league table when we try to look up 9. We need each rank to be unique for our league table to work.
We will need to use another formula to address this, and we can use
COUNTIFS to count how many instances have occurred so far in the list.and if this is greater than 1, to add the difference to the rank. Our formula becomes:
=RANK.EQ($B3,$B$3:$B$12)+COUNTIFS($B$3:$B3,$B3)-1
Notice that in the criteria range B3:B3, I have fixed the row on the start of the range and left it flexible on the end, so when our formula is copied down, we are always counting the instances from the top of the column to the current row. By the end of the range, our formula is:
=RANK.EQ($B12,$B$3:$B$12)+COUNTIFS($B$3:$B12,$B12)-1
On the first 24, our RANK.EQ function returns 8 as before, and our COUNTIFS counts that there is 1 24 so far, so:
8+1-1 = 8
On the second 24, our RANK.EQ function again returns 8, but our COUNTIFS now counts that there are two 24s so far, so:
8+2-1 = 9
thereby giving us the result we required:
The league table was created by entering the numbers 1 to 10 in the first column and using
INDEX and MATCH to return the number at that rank from the first table.
The INDEX/MATCH function on the first row (row 3) was:
=INDEX($B$3:$B$12,MATCH($E3,$C$3:$C$12,0))
If you did not understand the COUNTIFS or INDEX/MATCH functions, please visit these earlier posts:
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.
