Trying to get the opposite effect of LOG function

[RRAAC]

Hello,

I'm trying to weight a score so that the closer it is to the top the smaller the increments are. For example, if someone scored 2.1 then they would get 100, if they get 2.5 then say 99, then 3.5 might be 96 etc. the further away you get from 2.5 the more severe the drop in score.

When I ran the following table (see below) I used the formula

ROUND(LOG(A$2,A2)*100,0)
ROUND(LOG(A$2,A3)*100,0)
ROUND(LOG(A$2,A4)*100,0)
...
etc.

But the problem with LOG is that going from 2.1 to 2.5 is too severe a drop until around 12 where the decline is score becomes quite flat. I want it to start "flat" and get more severe the further down the list.

Advice would be much appreciated!

[TABLE="width: 174"]
<tbody>[TR]
[TD]Rate[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]2.1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2.5[/TD]
[TD]81[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]3.5[/TD]
[TD]59[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]4.5[/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]5.5[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]6.5[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]7.5[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]8.5[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]9.5[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]10.5[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]11.5[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]12.5[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]13.5[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]14.5[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]15.5[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]16.5[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]17.5[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]18.5[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]19.5[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

 

[DSCfromCFA]

G'day RRAAC,

I have experimented with your formula to get it to flatten at the top and then drop away and the best I could get is this
=ROUND(LOG(A$2,22.1-A2)*100,1)
however, that is probably way too flat at the top, 4.0 still being 99%.

I'm not enough of a mathematician to know how to go any further, but you could design your own curve and use vlookup to fill in the data.

Cheers

shane