I am trying to rate employees. In col C I enter their points from a test (from 0 to 10)
In col B I would like to return a "grade" from 1 to 4 based on how many points they get in col C based on the following condition: 0-2 points gives a grade 1, 3-7 point gives a grade 2, 8-9 points gives a grade 3, 10 points gives a grade 4.I can't figure out how to do that - can anyone help me?
Hi, The VLOOKUP function will do this for you.
First create a table with scores and grades
I used cells F2 to G12
Possible scores are in column F and the grade for the score is in the adjacent cell in column GF G 2 0 1 3 1 1 4 2 1 5 3 2 6 4 2 7 5 2 8 6 2 9 7 2 10 8 3 11 9 3 12 10 4Enter scores in column C
Enter the following formula in column BIf first score is in C2
then in B2 enter
=VLOOKUP(C2,$F$2:$G$12,2,FALSE)Note the $ signs.
The formula looks up the value in C2 in cells F2 to F12
Then, on finding a match it returns the grade from the second column of your table - hence the 2 in the formula
False ensures that the formula finds an exact matchDrag the formula in B2 down as many rows as you have scores.
Regards
Here's a classic (and rather longish) =IF() solution: =IF(C1=10,4,IF(AND(C1<=9,C1>=8),3,IF(AND(C1<=7,C1>=3),2,IF(C1<=2,1,""))))
Hi both Humar and Mike. Thanks for both of your repliles - they both did the trick in deed. I do appreciate your effort - you practically saved my day :-)
Søren, Denmark
You're welcome
