Ranking Data in Excel

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.

Last year, my employer ran an incentive campaign for our indirect channel; it was so successful that this year we’re running it again.

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.

  1. The cell to rank. e.g. A2
  2. The range of cells to rank within. e.g. A2:A14
  3. The order to rank in. e.g. 0 for descending, 1 for ascending.

Example of RankingIn the example show, I’ve taken 13 random figures (for luck) from 0.00 upwards and ranked them in descending order (i.e. the highest is #1).

The formula used is:

RANK(A2,$A$2:$A$14,0)

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s