I devised a worksheet to automatically calculate the points of our school ‘houses’ as part of a quiz competition we were having. Getting the results to tally was easy. Figuring out 1st, 2nd and 3rd placing automatically…was not!
After creating the world’s longest NESTED IF statement (which actually did work), I was rather frustrated that I did not (as I normally would but somehow forgot this time) go and search online if someone else had encountered the same dilemma as me and how they solved it.
I wanted to have a way to automatically show me who got first place, second place and then third place. The NESTED IF I created worked…but it seemed incredibly cumbersome to me and I surmised that there ought to be a better way. And there was!
RANK to the rescue
The RANK function allows you to select a number in a range of numbers and then it tells you where it ranks (position as in 1st, 2nd, 3rd etc.) in that range. It makes sense!
Here is my example:
You can see that I have sets of scores for each House. Using the RANK function I can automatically show which House is first, second and third.
I then used a VLOOKUP to lookup the RANK number and put the Houses in order for easier report back.
There may even be an easier way than this. But I am grateful to have discovered yet another truly useful Excel function!