Awarding Points on a Scale

[ChetKG]

Hi

Excel newbie here and would appreciate any help someone could give. I am trying to take points away (max 100) for exceeding a target value

Lets take the example a service loading

The team have a target of 350 (milliseconds) anything under that time gets you 100 points automatically, anything over 350 (milliseconds) loses you points from the 100 the further you are from magic 350 target until eventually you get 0 points.

Could anyone help with a formula/function that could do this?

Thanks

 

[steve the fish]

Hi. Yes you can do that but you have failed to say at which point you get zero.

 

[ChetKG]

Fair play lets say for arguments sake Zero can be achieved at 1000

 

[steve the fish]

Put a number in A1 and then try:

=MIN(100,MAX(0,100-((A1-350)*100/650)))

 

[ChetKG]

Worked a charm, thank you so much

 

[ChetKG]

So a further question I have is there a example of how I could use this on a score of out a 100

So getting 100% on the test gets you 100 points but as you slide down the % towards zero you get less and less points... basically if you get less than 60% on the test you get 0 points is what i would be after.