Calculating Student Grades

[drof_06]

Someone please help.
I need to be able to award a grade based on two results; Practical and Skills. Marks are awarded from 1 (being the lowest) to 50 (being the highest) and then a Level of Achievement from E (being the lowest) to A (being the highest).

Where I've been having trouble is calculating student grades with the following conditions:
A (ranges from 41 to 50) - but must have a minimum 38 in one and minimum of 41 in other
B (ranges from 31 to 40)- but must have a minimum 28 in one and minimum of 31 in other
C (ranges from 21 to 30)- but must have a minimum 21 in both
D (ranges from 11 to 20) - but must have minimum of 14 in either
E (ranges from 1 to 10) - has an average less than 10

I also need to rank class grades based on the average of the two results. The conditions restrict a standard average to occur. For example if a student received a 37 in one and 50 in the other their average would need to remain in the B range (i.e. no greater than 40) or if a student received a 20 in one and 50 in the other their average would need to remain in the D range (i.e. no greater than 20).

I really hope this makes sense to someone but please let me know if more information is required.

 

[Sandeep Warrier]

Hi & welcome to the Board!

I'm not sure I get your requirements. However, here is my attempt.

Excel 2003
	A	B	C	D	E	F	G	H	I	J
1	Name	Practical	Skills	Grade			Helper	Minimum Score 1	Minimum Score 2	Grade
2	Student A	38	41	A			10	10	10	E
3	Student B	37	41	B			14	14	14	D
4	Student C	25	30	C			21	21	21	C
5	Student D	29	32	B			29.5	28	31	B
6	Student E	20	21	D			39.5	38	41	A
7	Student F	10	18	D
Sheet1

Cell Formulas
Range	Formula
G2	=AVERAGE(H2:I2)
D2	=VLOOKUP(AVERAGE($B2:$C2),$G$2:$J$6,4,1)

I am not very clear on your second question.

 

[Jugantarji]

You could try this long and inelegant formula

=IF((AND((A1+B1)>=(41*2),(OR(AND(A1>=38,B1>=41),AND(A1>=41,B1>=38))))),"A",IF((AND((A1+B1)>=(31*2),OR(AND(A1>=31,B1>=28),AND(A1>=28,B1>=31))))),"B",IF((AND((A1+B1)>=(21*2),OR(AND(A1>=21,B1>=21),AND(A1>=21,B1>=21))))),"C",IF((AND((A1+B1)>=(11*2),OR(AND(A1>=14,B1>=14),AND(A1>=14,B1>=14))))),"D",IF((AND((A1+B1)>=(1*2),(OR(AND(A1>=10,B1>=10),AND(A1>=10,B1>=10))))),"E",0)))))

 

[drof_06]

Thank you so much for the suggestions. Both of these replies do a wonderful job of calculating the grade.

The second part of my calculations requires students to be allocated a value between 1 and 50 for their rank in the class. My apologies for not making this very clear but I'm not exactly sure how to word it.

Hopefully this makes a little more sense:
If a student received a 50 for both practical and skill then their rank would be 50 (average of their two results). This would work for most cases but where it becomes a problem is around the cut-offs.
If a student received a 38 and a 41 their rank would be go up to 41 but if a student received a 37 and a 41 (or greater) their rank would still remain at 40. This same ruling applies to all the other cut-offs. So at the bottom end, even if a student received a 10 and a 50 their rank value could not go higher than a 20 because of the restrictions placed on grades.

Hopefully this makes sense.

Thank you so much for your help

 

[Jugantarji]

You could try something like

=IF(AND(A1>=38,B1>=38,C1="A"),50,IF(AND(A1>=28,B1>=28,C1="B"),40,IF(AND(A1>=21,B1>=21,C1="C"),30,IF(AND(A1>=14,B1>=14,C1="D"),20,IF(AND(A1>=10,B1>=10,C1="E"),10,0)))))

Assuming A1 and B1 are your scores and C1 is where you have their averaged out grade

 

[drof_06]

Thank you so much for helping me with this question. I'm happy to say it's working how I need now

 

[Jugantarji]

Glad to hear it, and thanks for the feedback