I work for an education facility and a number of times throughout the year I have to manually check student grades for accuracy. I can have up to 40 spreadsheets with up to 300 students per sheet so it is quite a time consuming process. I am wondering whether a formula might help reduce the time I need to spend and also reduce the likelihood of error on my part.

Basically the documents come to me with 4 columns populated. A is the student's name, B is the student's ID number, C is the student's grade (ie HD for High Distinction), and D is the student's mark (ie 93). What I am hoping to achieve is that Excel will return some form of result in say column E if column D's figure doesn't correlate to what it should from column C's data. For example if cell C1 has HD within it, then only a range of 80 to 100 would be acceptable in cell D1. If the figure 63 was in D1 I would want to be flagged that there was an error so that I could follow up with the lecturer as to whether the grade or mark had been entered incorrectly.

The trouble is, there are 8 variables grade wise that need to be checked so I'm thinking a formula might be very messy, if not incredibly long. I have listed below what needs to be compared:

If NN is in C1 then D1 needs a figure in it between 0 and 49

If PP is in C1 then D1 needs a figure in it between 50 and 59

If CR is in C1 then D1 needs a figure in it between 60 and 69

If DN is in C1 then D1 needs a figure in it between 70 and 79

If HD is in C1 then D1 needs a figure in it between 80 and 100

If NS is in C1 then D1 must contain a value and can't be blank

If WT is in C1 then D1 has to be blank

If AN is in C1 then D1 has to be blank

and so on for C2/D2 right through to possibly C300/D300.

I have absolutely no idea whether this is even possible, and am only an intermediate user of Excel, so please excuse my ignorance for asking what could well be the daftest question ever asked lol.

I would be so grateful for your assistance, and thank you all in advance.

Sue