Ranking and Allocating points

[manfareed]

Hi ,

I have a finance spreadsheet which ranks various divisions by revenue
growth. I managed to rank them according to % growth but now I need to give
each branch ranking points.
The highest ranked division will get a 1000 points and the rest will get
points in proprtion to their rank. It was working "ok" until recently when
some divisions started getting negative % growth in revenue compared to last
year. The formula I was using was %growth in revenue/ highest % growth in
revenue * 1000.

Please see below

Thanks

2008 Highest Ranking Ranking Points
Gross Margin
% Value
Growth
1.2% 1.2% 1
1.1% 1.2% 3
-29.6% 1.2% 9
-18.4% 1.2% 5
-47.5% 1.2% 15
-32.4% 1.2% 13
-26.9% 1.2% 7
-30.8% 1.2% 11
-58.6% 1.2% 19
-49.8% 1.2% 17

 

[Pete_UK]

It looks strange that you only have odd numbers in the ranking. What
formula are you using?

One way you could allocate points is via the ranking values
themselves. Suppose you have nine divisions to rank - you could have
something like this:

Rank Rev_rank points
1 9 20.0%
2 8 17.8%
3 7 15.6%
4 6 13.3%
5 5 11.1%
6 4 8.9%
7 3 6.7%
8 2 4.4%
9 1 2.2%

The second column, reverse rank, is just =10-A2 copied down in this
case, but you can derive this directly from the RANK function. In C2
is this formula:

=B2/SUM(A$2:A$10)

and then copied down. You can just multiply column C by your 1000
points to determine what each gets.

Hope this helps.

Pete

 

[Shane Devenshire]

Hi,

You're going to need to give us more info. For example why are all the
numbers in the second column 1.2%? What formulas are you using? In fact why
are you even using a column with 1.2% all the way down?

 

[Pete_UK]

That 1.2% looks like MAX(A:A), though I don't know why it is there.

Pete

 

[manfareed]

Hi,

Thanks for your replies.

1.2% is a max formula which gives the highest gross margin increase. I might
not actually need this anymore. Previously I used this value for the "ranking
points" formula which was gross margin/highest value *1000.The ranking should
be based on the first column.

The line with highest rank should have the maximum points of 1000. Problem
is allocating points to others.

The formula i used for the rank column is =RANK(I38,$I$38:$I$57). I38 is the
first line in the table to be ranked. I am not sure why values are all odd
numbers.

Hope the above helps.

Thanks