Adding a sumif to an existing formula

[sloan3]

Hello all,

I am not sure if I am posting in the correct place... I teach weightlifting in a school system and would like to chart and grade students based on performance. Ultimately I have created a range of grades applied over a range of pounds lifted. The issue that I am stumbling over I believe is the addition of another IF function. I would like to add points to grades for students that fall withing a certain body weight range. For example if a student is under 115 pounds body weight, I would like to add 15 points to his grade (kind of a curve). Once I figure out the rule I can then go in and complete the total function. The current working formula that I have (example bench press) is:

=IF(E96="","",IF(E96>194,"100",IF(E96>164,"92",IF(E96>134,"85",IF(E96>104,"76","69")))))

Formula says that if he lifts over 194 pounds he receives a 100 for a grade down to a 69 if he lifts less than 104 pounds. E96 represents his raw lifting score. While not listed in the formula, I do have a box designated for body weight. For this student the box for body weight is K94.

With that being said, this particular student weighs only 112 (K94) pounds and he lifted 115 pounds (E96). Based on my grading scale he received only a 76 for a grade.

So, how can I add points to a grade: 15 points if body weight is under 115 pounds, 10 points if under 130 pounds, and 5 points if under 145 pounds. No bonus points if body weight is over 145. These are examples.... Again, I am just learning how to use EXCEL and would like some clarification.

Thanks.
Scott

 

[Peter_SSs]

Welcome to the MrExcel board!

Instead of building all the values into the formula, I would make a couple of lookup tables and refer the formulas to those tables. I have put them nearby on the sheet below but they could be anywhere on the sheet, or even another sheet, and they could be hidden if you want.

This may need a little tweaking (post back with details if you need help) but should go fairly close I think.

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
93
94							Body Weight:	112		Bench Press	Points		Body Weight	Bonus
95										0	69		0	15
96	Bench Press:	115								104	76		115	10
97										134	85		130	5
98	Grade:	91								164	92		145	0
99										194	100
100
Weightlifting