Q on ranking columns

S

Smul182

Hi everyone,
I hope someone will be able to help me with this question. Ok, Lets se
if I can explain this correctly. I am making a spreadsheet for wor
(manage a lowes). There are 19 departments and i want to rank them i
different catorgories. ie.. sales this year vs. last year ranked 1-1
on pct up vs. last year. next ranking might be margin this year vs
last year ranked 1-19. I then Want to add up the ranking from about
or 6 different catagories to find the overall best department.
any hints or suggestions would be greatly appreciated
God Bless
Scot
 
B

Biff

Hi Scott!

The first thing you need to do is to calculate the
differences from this year vs last year for your
catagories.

Assume the 19 depts are in cols A:S.

The calculation for one of your catagories is in the range
A50:S50 and you want to rank that catagory. So, in A51
enter this formula and copy across to S50:

=RANK(A50,$A50:$S50)

This formula does not account for ties. If you want to
break ties:

=RANK(A50,$A50:$S50)+COUNTIF($A50:A50,A50)-1

This will break ties but the first instance of a tie will
get a higher rank than the next instance of a tie. eg:

12 and 12 are both ranked 7th. The first 12 will get
ranked 7th and the second 12 will get ranked 8th.

Repeat this for all the different catagories. To find the
best overall dept., you could add all the individual
rankings and the depts with the lowest amts are your best
depts. Or, you can just rank the sum of the rankings!

Biff
 
Top