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.