Excel Ranking Help

Martynt74 · 17 Oct 2011 at 11:55

Hi

I’m trying to set up a spreadsheet as shown below. I used to only have a “Current Month” and “Previous Month” fields which made ranking very easy as I only had 2 columns. However my girlfriend now wants to have yearly totals included.

I figured to do this I need to have 2 external fields which have the current and previous months (as I have in cells B1 and B2) and then use some kind of lookup to those cells to then pick out the ranking of the correct months column. So that is B1 showed Oct-11 then it would rank based on Column E, however if B1 changed to Apr-12 then it would calculate the ranks based on Column K

Is this do-able? As its for the mrs I’d rather it auto-calculate the ranks rather than her changing the Ranking formulae each month.

Participant · 17 Oct 2011 at 12:20

In cell Q5, enter:

Code:

=LOOKUP($B$1,$E$4:$P$4,$E5:$P5)

Similar deal in R5, except refer to B2 instead of B1, then copy the formulae all the way down the columns. I think that should do what you need.

Martynt74 · 17 Oct 2011 at 12:27

Ah ok, i get you. So have 2 additional columns with the current and previous months figures and then use the rank function off those rather than trying to have a formula do the lookup and ranking together.

Cheers

Participant · 17 Oct 2011 at 13:48

That, or make each month a named range then nest the LOOKUP within the RANK:

(There might be a clever way to do this without having to name each month).

Participant · 17 Oct 2011 at 14:15

...which there is (took me a while

):

Code (if I'm allowed to call an Excel formula 'code'):

Code:

=RANK(LOOKUP($A$1,$B$4:$J$4,$B5:$J5),INDIRECT(ADDRESS(5,(MATCH($A$1,$B$4:$J$4)+1))&":"&ADDRESS(1000,(MATCH($A$1,$B$4:$J$4)+1))))

This matches whatever date you have in your feed cells, looks along the row with dates in it, then looks down the matching column and ranks the number. No need to define named ranges, just enter the dates and it should sort itself out.

Martynt74 · 17 Oct 2011 at 14:29

Fantastic, i'm sure you help in every Excel thread. Sure you don't work in their development team!

Participant · 17 Oct 2011 at 14:32

Haha, yep, I'm just a lazy accountant that likes to shorten every possible task in Excel and is constantly looking for novel ways to be even more lazy

.

Martynt74 · 17 Oct 2011 at 14:42

Haha, since i'm training to be an accountant as we speak i hope to one day reach your level or laziness (and knowledge!)