I assisted with a school Excel spreadsheet where the teachers had multiple students from multiple schools do various eisteddfod pieces.
The objective was to make the file easy to fill in while being reusable for following years. Which is easy enough with VLookUP’s, Sheet Protection and Drop Downs. One of the requirements though was to clearly indicate the Winner of each specific event.
To do this we require something that will be ranking numbers against the marks received by each student.
RANK.EQ Function
The RANK.EQ Function as per Office.com describes the Function as:
Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned.
If you were to sort the list, the rank of the number would be its position.
There are two ways of specifying the data you want to see.
Firstly you can use the full Syntax:
=rank.eq(NumberYouWantToRank, TheListToCheckAgaints, SortOrder)
Secondly, you can omit the sort Order:
=rank.eq(NumberYouWantToRank, TheListToCheckAgaints)
With the second syntax, the order will be automatically set to Descending.
Example
So since I don’t have the copy of the school excel file as mentioned above. I created a quick demo file.
The file is generated with the functions Rand and Randbetween to show it can work with Decimals. As per below screenshot you can see the formals used.
Below is the Data Generated by each Formula. As you can see the ranking of the numbers next to it but not in any order.
Because the formulas to Randomly generate numbers changes every time something changes on a sheet I copied the RandBetween and Rank.EQ columns and Pasted the Values next to it.
Separately you will then see the numbers in order one through ten as the representation of the order the numbers need to be in. I then sorted the Rank.EQ (Values Pasted) field from Smallest to Largest.
Looking through the sorted Rank you should see that four as rank is missing. This is because there is a duplicate and duplicates Rank the Same.
Rank.EQ will then skip the following Rank (Four) and move onto the next Rank. If there are multiple Duplicates it will skip multiple Rank’s.
This will give you a true representation that Rank seven (although there are multiple numbers on the same rank) has six other higher\Lower qualifying numbers.
Rank vs Rank.EQ
For the people that caught it you would see in the original formals there are two Formals, Rank and Rank.EQ. In the older version of Excel only Rank existed. In the new version of Excel, they split Rank into two different functions, Rank.EQ and Rank.AVG. Rank.EQ is exactly the same as Rank.
Rank.AVG
The difference with Rank.AVG is how it handles duplicate values.
If there are duplicate values and they rank (three, four and five). Instead of making the rank for all of the numbers three. It rather counts all of them together and divides is by the number of Duplicates. Thus it gives you the average number for all of them. (3 + 4 + 5) / 3 = 4.
