#### drof_06

##### New Member
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.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi & welcome to the Board!

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

Excel 2003
ABCDEFGHIJ
2Student A3841A101010E
3Student B3741B141414D
4Student C2530C212121C
5Student D2932B29.52831B
6Student E2021D39.53841A
7Student F1018D
Sheet1
Cell Formulas
RangeFormula
G2=AVERAGE(H2:I2)
D2=VLOOKUP(AVERAGE(\$B2:\$C2),\$G\$2:\$J\$6,4,1)

I am not very clear on your second question.

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

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

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

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

Glad to hear it, and thanks for the feedback

Replies
1
Views
400
Replies
8
Views
157
Replies
15
Views
476
Replies
6
Views
298
Replies
8
Views
370

1,196,235
Messages
6,014,146
Members
441,807
Latest member
sjkenjalo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back