It isn’t often I feel like a total n00b when it comes to software.
Actually, that’s a lie. I learn quite a lot throughout the working day, that’s one of the attractions of the job I have.
Little nuggets of knowledge occasionally filter in like techno-trinkets spilling out of a software codemine.
For last year’s campaign I set up a simple spreadsheet to track the progress of the participants. This spreadsheet acted as a middle tier from the back end database to the online data mart driving the display that the participants had access to.
This year, I’ve improved the spreadsheet (I hate spreadsheets, I’d much rather do it all in Microsoft SQL Server but time is a precious commodity) but for the past few months have continued to determine the order of the leader-board by hand before passing the data through to the data mart.
So this morning I decided to see if Excel could rank the leader-board for me; and without any surprises, it can, quite neatly…
…by using the RANK function in a formula.
RANK takes 3 parameters.
- The cell to rank. e.g. A2
- The range of cells to rank within. e.g. A2:A14
- The order to rank in. e.g. 0 for descending, 1 for ascending.
The formula used is:
I’ve then dragged this down the column (the dollar ($) signs ensure that the cells within the range don’t move when I drag the formula).
Simplicity itself, I can’t believe I haven’t come across the RANK function before.
I guess I’ve just never needed it before.