Wondering whether a formula could assist or is even possible ...

[eskimo85]

Hi all

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

 

[pjmorris]

Ohhh, just realised my previous idea for the formula didn't take account of about half of the thread!!! I think the idea is still OK, but need to use Eric's most recent formula with the NOT removed.

Sorry for butting in.

 

[eskimo85]

Hi Eric and Peter

Thank you both for your responses. Peter, unfortunately the checkpoints are set in stone in that the sheets go through various stages before I to go through them and then send the ok to the Dean for sign off. Part of my role decrees that I sign off that I have physically viewed and signed off on each of the sheets, so I have to check them anyway. Whilst it would be wonderful to remove myself from this step, unfortunately I can't. But I'm very grateful that you took the time to contribute. It's all learnings at my end which is great

Eric, thank you very very much again. I have only been selecting the cells in the column with data (rather than the whole column) so it's good to know that the changing to 2's was on the right track.

Ta also for picking up the ISNUMBER error and for checking through it for me. Whilst a shorter formula would be nice, I'm kind of use to this one now and am understanding it a bit more, so to save you work, I think I'll just stick with it - it's easy enough to just copy and paste and it really does work wonders. Ta so much for your offer though!! Start of the day for me here so I had better get cracking on work ... hope you got a doubly good sleep in your reduced hours - you're my hero!!

Sue

 

[Eric W]

Peter: No worries about butting in. That's a nice thing about this forum, is that people can jump in with different ideas, there's never just one answer. Sometimes someone comes up with an idea that leaves everyone scratching their heads saying, "Why didn't I think of that?" But, as you saw, sometimes it's hard to catch up to all the posts when it's a long thread.

Sue: Sounds like you're all squared away! If you have more questions, you know where to find me. Hope you have a lovely day!

-Eric ?