Weighted Ranking

[comish4lif]

I am trying to work out a formula to calculate a weighted ranking. But
I cannot get it to award the exact number of points...

I want to rank 13 items - the worst value would always receive 1 point,
the best value would always receive 13 points. The values in between
would be based on how their amount related to the best and last. That
is if you missed having the best value by 1, your weighted rank should
be something like 12.9. If you were 50 points back, maybe something
like 10.3.

Here's a sample column:

235...13
201...9.5 <-- the formula here is: =13-((($B$3-B4)/($B$3-$B$15))*12)
195...8.8
194...8.7
193...8.6
187...8
174...6.6
169...6.1
169...6.1
166...5.8
124...1.4
124...1.4
120...1

This looks fine on the surface, but the points add up to 85.1, and
should total 91 points (13-12-11...+1), so, my formula is not weighting
something just right....

In the formula, $b$3 refers to the top left cell (the high value = 235
in this example), B4 is the cell on the current row (in this case,
value 201) and $b$15 is the bottom left cell (the low value 120).

Any thoughts?

 

[Pete_UK]

Imagine the situation where 11 of your numbers are 234, with 235 being
the top and 120 the bottom (ok, unlikely, but bear with me). You would
then have 11 values of 12.9, and 1 of 13 plus 1 of 1, giving a total of
155.9, so I don't know why you think the total should be 91 points in
this case - surely you will only get that if the ranking formula
applies equal weight to the positions of the scores and not to the
scores themselves. You can do this with the RANK( ) function.

Hope this helps.

Pete

 

[comish4lif]

In the case where 234 is the best, 120 is the worst, and all 11 in
between have the same value - then they should all have the same
weighted points (7.0) - because they all make up the same percentage of
the difference.

And I think this is how it should work now....

The Numerator is the value of that row less row 13 (last place).
The denominator is: Calculate the sum of 2nd-12th place - the 11 items
in the middle. Then Subtract 11*13th place value from that number.
The result of this division is the percent that the value of that row
contributes to the spread of the category.
Multiply this by 77 (the remaining value of weighted points to be
awarded (91-13-1=77)) and it mostly works.... values to close to last
place are less than 1 point. Take the 77 and replace with 66 and add 1
to each answer... that gets me close enough...

 

[Pete_UK]

Another way of thinking about it: You have values of 13 down to 1, but
the outer values are fixed, so 11 places have to share the values 12
down to 2, i.e. 77 points. However, the lowest value for each member of
this group of 11 is 1 (they can't be lower than the lowest!), so you
only have 66 points to share between them, and the "share" for each has
to be added to 1.

More or less the same conclusion you arrived at.

Pete

 

[kcc]

Since it's not linear, the results have to be skewed to fit and
there are a million ways to do that. Here is a simple way.
Assuming the current formula is in C, in D315 put
0,1,2,3,4,5,6,5,4,3,2,1,0
In E3 put =C3+D3/$D$17*(91-$C$17)
C17 is the sum of column C and D17 is the sum of column D.
Seems to produce reasonable values.
kcc