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

 

[sykes]

Morning Sue (from the other side of the world!!)

That's great news. Well done!

As Eric alluded to, I'm sure the 6-digit student ID format could be sorted at the same time, for you. I wouldn't be surprised if he couldn't write it into the same conditional formatting formula, for you - or if not, add another one.
Certainly my coded solution could easily do this, just by adding another line or two, so do come back to me if you need to do this - or just fancy trying out the code, anyway.

In the meantime, please, keep being verbose when posting problems - I'd guess that at least half of the initial questions on this site, start with a short post, then soon descend into a long diatribe of ping-pong posting, whilst we rub our crystal balls - and try and get to the bottom of the poster's workbook set-up, and try to prise out of them what the desired result really is!
Another huge gripe of mine, is that 15 to 20 posts down the line, they're actually after a lot more - and if only they'd been clear from the outset, it would have saved loads of nugatory effort!

Anyway, enjoy deploying Eric's solution when you get to work, and as I said, come back here if you fancy a bash at coding.
All the best.
Sykes.

 

[Eric W]

I'm very glad it works for you! Thanks for the interesting problem, and for the follow-up on it. If you ever have another question, let us know. Next time I'm in Tasmania, I'll look you up!

 

[eskimo85]

Hi Eric, yes you must!

Funny you should say that (another question) as I thought of one overnight - although it still relates to the original question. It should have occurred to me earlier - I don't know why it didn't. In doing my dummy test sheet I only populated 16 or so rows which made it clear at a glance which ones were incorrect, however given that there is up to 300 rows on the real sheets I thought it might be quicker if I could filter somehow the rows with errors after the conditional formatting has highlighted them so that I can take a screen capture of only the ones that have problems to email back to the relevant Course Coordinator to follow up with the respective lecturers. Does that make sense. Still quite early here on Monday morning and I don't know that I'm fully awake yet LOL. Is that something that would be achievable do you think?

Sue

 

[eskimo85]

I sure will sykes, once things quieten down a bit I'd love to learn more. Thank you ever so much again

 

[Eric W]

Sure, you can filter the rows. Just take the original formula and put it in E1. Then copy it down the column to the end of your data. At this point, you might want to put in a header row, since the Filter tool expects one. But if you don't, you'll just see row 1 whether or not it should show. Then just select column E, click on Sort & Filter > Filter. Then in the drop-down box that appears, check the TRUE box and uncheck the others. All done!

Hopefully, your Tuesday is a bit better! (Although I'm just starting my Monday )

Good luck!

 

[eskimo85]

Thanks Eric, that's terrific - will make things that much easier rather than having to note down all the errors (of which I hope there aren't too many) in an email. A visual screen capture can be emailed instead. Anything to save a bit of time in what has been in my view an unnecessarily long process from go to wo. You're all champions, really you are

I hope your Monday proved to be a good one. And might I say, if ever I come to the States I really must visit your town - what a lovely sounding place to live in!!

Sue

 

[eskimo85]

Hi Eric, just a quick question if I can? The Exams Office have added in a number of new columns to the results spreadsheets along two initial rows which has resulted in my data now being in columns P & Q, and commencing on Row 3 rather than Row 1. Would I be correct in assuming that if I change all instances of C & D in your provided formula to P & Q, and amend to 3 rather than 1 (eg C1 would become P3) that it should all still work correctly. Just wanted to make sure before I break everything LOL. Ta

 

[sykes]

Hi Sue

Could be that Eric's busy / offline etc; in his absence, I suggest that you make a copy ("SaveAs") and mess around on that, if you're concerned about compromising the original data.
You can either copy the whole workbook (or .CSV file) or just right-click the sheet's name tab, and check the "Create copy" checkbox - then work on that copy of the worksheet in your file. If it all goes to plan, implement the changes into the real sheet/book.
Another way, if the extra columns/rows that the Exams Office have added, aren't needed for what you're doing, is to just delete them again (on a copy, perhaps), so your data's back to it's original rows & columns. Easiest way to delete a whole row/column, is to click on either the number to the far left (row) or letter at the very top (column) then right-click, and select "Delete." Sorry If I'm teaching Granny to suck eggs, as we say in England!
In the end, it all depends in what state you have to send things back out to other people.

 

[eskimo85]

Thanks so much sykes; lovely to hear from you and ta for your response. As it turns out, I ended up doing the very thing you suggested not long after the last tome of a post lol - great minds think alike it seems; well one great and one not so great and a wee bit tired It's been a doozy of a week so far. So all good in that regard now. All is working a treat.

Can relate to your English saying - my Mum, her siblings and parents came over to Australia in 1956 as '10 pound Poms' - Mum's words not mine, so I reckon I've probably heard every English saying there is over the years. One day I'll get there - certainly on the bucket list!

Sue

 

[sykes]

Glad you're sorted, Sue.
You should be over here now - first summer for years, when it's not rained most days! I'm even thinking of dusting off the old BBQ - just need to remember how to use it!