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,""))))

MIKE

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

Ask Your Question

Weekly Poll

Do you think cellular roaming fees are unfair?

Discuss in The Lounge

Poll History