Calculate a scored number based on a percentage

[Skiczn]

Hi there,

I score my staff on percentages and our Global Head Office require a score out of 5 to show our KPI's.

I had set up a spreadsheet so that anything >=100% equals 5, 92% equals 4, 84% equals 3, 76% equals 2 and 68% or anything less that 68% equals 1. (1 being the lowest score)

Now that I am setting it back up again though I cannot recall how I had the formula. I had it so if I typed in 98% it would automatically calculate and show 4.75. It was infinite, so no matter what percentage I typed in, it would show me the conversion.

However now that I am trying to set it up again I cannot find how I did this previously. Apologies, I set all these things up about 6 years ago so cannot recall how I did it and they were "accidentally" deleted while I was on long service leave.

I have attached the score card for reference. In the example I have highlighted E6 to J6 where the score is supposed to be and you can see the percentages in E7 to J7. Any Help is appreciated.

thanks

[avk]

However now that I am trying to set it up again I cannot find how I did this previously. Apologies, I set all these things up about 6 years ago so cannot recall how I did it and they were "accidentally" deleted while I was on long service leave.

I have some confusion as per your comment.
If you have enter manually data with expected result it will clear idea.
Are you required data since from last six (6) years. If yes then you will need to change your format in sheet "Scores" & "ScoresCard".

[JohnTopley]

Try

=(5-(1-MAX($A1,68%))*12.5)

where A1 is % score

See attached: sheet "Working"

[Skiczn]

Try

=(5-(1-MAX($A1,68%))*12.5)

where A1 is % score

See attached: sheet "Working"

PERFECT!!! Thank you John! Exactly what I was looking for! I really really appreciate the help