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 D3:D15 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

<comish4lif@verizon.net> wrote in message
news:1140107944.186950.12370@o13g2000cwo.googlegroups.com...
>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?
>