Percentile formula help

[Nubian]

Hello,
I am trying to use formula to automate a scoring card. There are 4 categories each worth 25%. Each category has 5 ratings as below

"Poor (0% score)"
"Fair (10% - 50%)"
Good (50% - 80%)
"Excellent (80% - 100%)"
"Outstanding (100% - 120%)"

For example: If you manually enter a score of 120% in B5 I'd like the total score for that category to be 25% in B6

let me know if this is not clear. Thanks

[vlady]

hello like this?

scoring.xlsx

[CheshireCat]

Hi vlady,

You'll need a small adjustment in your LOOKUP formula:

Please Login or Register  to view this content.

Otherwise, any score below 10 will result in an error.

[vlady]

missed that one.. thanks CheshireCat, appreciate it.

[Nubian]

Thanks for your speedy replies, I have updated vlady's sheet with an example

[Spencer101]

Could you post an example worksheet with a few figures manually entered with explanation as to why those would be the results?

[martindwilson]

you also need to clarify your boundaries
Poor (0% score)"
"Fair (10% - 50%)"
Good (50% - 80%)
"Excellent (80% - 100%)"
"Outstanding (100% - 120%)"
you cant have the same say 50% in both fair and good

[Nubian]

apologies, from your comments I realize I have not been clear at all. and assume people are thinking what I'm thinking....

I'll attach another .xls for example.

To note the top score is 25 but they can overachieve to 30.