Excel: Return value based on another value

September 27, 2009 at 17:22:11
Specs: Windows XP

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?


See More: Excel: Return value based on another value

Report •


#1
September 27, 2009 at 18:03:53

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 G

	F	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	4

Enter scores in column C
Enter the following formula in column B

If 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 match

Drag the formula in B2 down as many rows as you have scores.

Regards


Report •

#2
September 27, 2009 at 19:26:32

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

http://www.skeptic.com/


Report •

#3
September 28, 2009 at 07:38:28

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


Report •

Related Solutions

#4
September 28, 2009 at 08:12:47

You're welcome

Report •


Ask Question