VLOOKUP Help for Report Card Grades Incentive Program

[mcgonma]

I have made an incentive deal with my daughter where she will earn money for her grades.

The incentives are
A+ (100) earns $1
A (99-90) earns $0.50
B+ (89-85) earns $0.15
B- (84-80) earns $0.05
NHI (Not Handed In) deduct $0.50 for each individual

I'm looking to do a lookup where I have a table with my daughter's grades column E (rows 2-30), column H (rows 2-7 has the grade range listed above, and column I (rows 2-7) has the incentive payout amount for each grade range.

In column G, I would like a formula that looks at the earned grade in column E, then checks column H to find the grade range that it falls in, and then finds the corresponding payout amount for that grade range and puts it in column G.

The word description for cell G2 would be something like: For the grade found in cell E2, find the range that it falls between in H2-H6, return the corresponding value in I2-I6. For example, if the grade in column E is a 92, the value returned in column G would be $.50.

 

[robertdseals]

I'm looking to do a lookup where I have a table with my daughter's grades column E (rows 2-30), column H (rows 2-7 has the grade range listed above, and column I (rows 2-7) has the incentive payout amount for each grade range.

In column G, I would like a formula that looks at the earned grade in column E, then checks column H to find the grade range that it falls in, and then finds the corresponding payout amount for that grade range and puts it in column G.

The word description for cell G2 would be something like: For the grade found in cell E2, find the range that it falls between in H2-H6, return the corresponding value in I2-I6. For example, if the grade in column E is a 92, the value returned in column G would be $.50.

Will the grades be in letter format (i.e. B+) or number format (i.e. 97)?

 

[mcgonma]

Will the grades be in letter format (i.e. B+) or number format (i.e. 97)?

The grades will be numerical.
Also, if the grade is below 80, there is $0 payout. And, if the grade is NHI it is -$.50.

 

[robertdseals]

Instead of doing a lookup, why not nest the formula? Something like....
=IF(E1=100,1,IF(AND(E1<100,E1>=90),0.5,IF(AND(E1<90,E1>=85),0.15,IF(E1="NHI",-0.5,""))))

 

[mcgonma]

Instead of doing a lookup, why not nest the formula? Something like....
=IF(E1=100,1,IF(AND(E1<100,E1>=90),0.5,IF(AND(E1<90,E1>=85),0.15,IF(E1="NHI",-0.5,""))))

Because if i change the payment amount for each range I need to manually change the formula instead of just changing the payment amount in a table. For example, the question I posed is for individual assignments in a given class; but we also have different payout amounts for her overall quarterly grade in each class, as well as her final year grade for each class.

 

[robertdseals]

You can put the range in separte cells (i.e. 100 in cell A1, 90 in cell A2, 85 in cell A3) and same with the $ amounts, then just reference those cells in the formula. Not elegant, but should work. Hopefully someone can come up with something better. Good luck!

 

[mcgonma]

You can put the range in separte cells (i.e. 100 in cell A1, 90 in cell A2, 85 in cell A3) and same with the $ amounts, then just reference those cells in the formula. Not elegant, but should work. Hopefully someone can come up with something better. Good luck!

now i see what you're saying and i think that could work. I'll modify what you provided and see if i can make it happen.
Thank you very much!!!

 

[Sufiyan97]

Is that what you want?

Book3
	E	F	G	H	I	J	K
1				Grade	Incentive
2		95	0.5	0	0	A+
3		85	0.05	84	0.05	A+
4		86	0.05	89	0.15	B+
5		0	0	90	0.5	A-
6				100	1	NHI
7				NHI	-0.5
8
9
10
11
Sheet3

Cell Formulas
Range	Formula
G2:G5	G2	=VLOOKUP(F2,$H$2:$I$7,2,1)

 

[mcgonma]

You can put the range in separte cells (i.e. 100 in cell A1, 90 in cell A2, 85 in cell A3) and same with the $ amounts, then just reference those cells in the formula. Not elegant, but should work. Hopefully someone can come up with something better. Good luck!

This was perfect and extremely helpful!!!