Assigning scores in Excel 2

[mkuethe]

I'm creating an employee performance database and need to know how to assign a score dependent on a percentage.

For instance, if an employee achieves between 99.8% and 100%, the score should be "5". If the employee achieves between 99.5% and 99.79%, the score should be "4" - and so forth. The formula would need to look at the one percentage and determine which score to give (between 1 & 5).

Can anyone assist me with this?

 

[carrr]

Use vlookup:

http://www.usd.edu/~kbrady/vlookup.html

http://www.oid.ucla.edu/Tatp/downloads/excel.pdf

"'Tis an ill wind that blows no minds." - Malaclypse the Younger

 

[mkuethe]

I tried that, but obviously I'm doing something wrong. Here's my scoring scale:

100.00% 5
99.80% 5
99.79% 4
99.50% 4
99.49% 3
99.00% 3
98.99% 2
98.50% 2
98.49% 1
0.00% 1

Any percentage I'm putting in is returning a "#NA" with the exception of 100%, which is returning a "1". I've tried messing with the formatting of both the percentage and the scoring table (both formatted as percentages, one formatted as percentage and the other as number, etc.) but nothing seems to work. Any ideas???

 

[Crystalyzer]

Just my two cents. Have you checked the formatting of the % in both the vlookup and your data table? If they don't match you would get the #N/A error.

 

[Crystalyzer]

I got this to work with DUMB (<---emphasis added) LUCK. All I did was sort your data table in ascending order by % and the formula works!

 

[mkuethe]

Yes. I've tried formatting both as percentages, both as numbers with decimal places, nothing works. That's why I'm struggling so much. I can't figure out what I'm doing wrong.

 

[Crystalyzer]

See my second post

 

[mkuethe]

Got it - It worked! Thanks so much!!!

 

[KenWright]

Just another way:-

with your value in A1, in any other cell

=LOOKUP(A1,{0,0.985,0.99,0.995,0.998,1},{1,2,3,4,5,5})

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------