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

 

[tyija1995]

Hey,

With the first grade in C2 and mark in D2 try this in E2:

IF(AND(C2="NN",D2<50,D2<>""),"",IF(AND(C2="PP",D2<60,D2>=50),"",IF(AND(C2="CR",D2<70,D2>=60),"",IF(AND(C2="DN",D2<80,D2>=70),"",IF(AND(C2="HD",D2<=100,D2>=80),"",IF(AND(C2="NS",D2<>""),"",IF(AND(C2="WT",D2=""),"",IF(AND(C2="AN",D2=""),"",IF(AND(C2="",D2=""),"","FLAG")))))))))

There is probably a better way though!!

EDIT: SUMPRODUCT might provide a better way.

You'll need to setup a grade and 2 key fields (lower and upper bounds)

=IF(SUMPRODUCT((C2=$I$2:$I$9)*(D2>=$J$2:$J$9)*(D2<=$K$2:$K$9))=1,"","FLAG")

Where I2:I9 are the Distinct Grades (NN ... AN).
J2:J9 is the lower bound key (0, 50, 60, 70, 80, 1, "", "")
K2:K9 is the upper bound key (49, 59, 69, 79, 100, 100, "", "")

Where I put "" just leave the cell blank (WT & AN should have blank keys)

 

[sykes]

Hi Sue

Firstly, Welcome to Mr. Excel!
Secondly, well done for writing your question in an unambiguous way - spelling out not only your scenario, but also exactly what the expected outcome is. This save s a great deal of wasted time on the parts of Folk who are willing to help!
Thirdly, far from being a daft question, this is EXACTLY the sort of thing Excel's made for, and what it's good at; I applaud you for recognising a very repetitive, time-consuming process, and one which is just begging for some human error; I also applaud you for wondering whether or not this could be automated. I'm sure that globally, billions of office hours each year, are wasted by folk who just carry on plodding through the same old ritual they've always done - not considering better use of technology.
As per the previous post, you can see that there are ways of achieving what you want.
There are normally also many ways of achieving the same thing.
I've come up with a solution which uses a button which, when pressed, goes down through all the used-"C" cells in the active worksheet, and tests the corresponding "D" cell against your criteria. If the D cell fails, it's background's turned red.
You can press it as many times as you like, so that, as the problems are resolved, each press of the button results in less & less red D cells.

As you're a self-declared "intermediate user" I won't post the whole solution yet - as it'll take a little effort to talk you through the process; if you'd like to have a go, post back here, and we'll go through it together.
Either way, well done!!

 

[eskimo85]

Good morning tyija1995 and sykes, and thank you both SO much for your replies. Unfortunately I had nodded off before you had both responded otherwise I would have got back to you both straight away. I love the sound of both of your solutions - how lucky am I to be offered such great advice & here I was thinking it may not be able to be done. Oh to have both of your brains!! lol. sykes, thanks for your very kind words, they were much appreciated as I almost didn't post thinking amongst all you Excel gurus it might sound a bit silly, but as you say, I suppose that's what help forums are for hey . I am leaning towards your button option sykes and would love to discuss it further. I am fairly busy all day today but would like to tackle it from tomorrow onwards if you have some spare time to assist over the next few days. If not, I completely understand.
Thank you both again, Sue.

 

[Eric W]

Another option to consider is Conditional Formatting. The results are similar to what sykes proposes, that the bad values would be highlighted in red (or a color of your choosing). The advantage is that you don't even have to press a button. If the Conditional Formatting is applied to the spreadsheets before the lecturers enter the grades, then they would see errors as soon as they entered them.

If you'd like to try that, open a copy of one of your workbooks. Click on the D in the column headings to select the whole column. Click Conditional Formatting > New Rule, Use a formula > and enter this formula:

=NOT(IFERROR(CHOOSE((SEARCH(C1,"NN.PP.CR.DN.HD.NS.WT.AN")+2)/3,AND(D1>=0,D1<=49),AND(D1>=50,D1<=59),AND(D1>=60,D1<=69),AND(D1>=70,D1<=79),AND(D1>=80,D1<=100),ISNUMBER(D1),D1="",D1=""),0))

then click Format... and choose your fill color.

The formula looks pretty long, but it's really not too bad. It first looks for the grade from C1 in the red list. Then based on the value, it looks up the proper condition from the later sections. NN corresponds with AND(D1>=0,D1<=4), PP corresponds with AND(D1>=50,D1<=59), etc. Then play around with changing grades and figures.

Try it out and let us know. Based on your circumstances, a macro like sykes proposes still might be worthwhile.

 

[eskimo85]

Thank you Eric for responding and your contribution ... boy oh boy I appear to be spoilt for choice don't I! How wonderful. Who would have thought. I promise to try all options out and will feedback on sykes' option too once we've run through it. Will certainly keep you all updated. Thanks so much again

 

[sykes]

Hi Sue
Spoilt for choice, indeed!! That's what you get for posting clearly, and accurately...

Whilst I put some work into getting my VBA solution ready for you, I actually believe that Eric's idea is a better one.
It's much easier to implement, it doesn't require running code (which means that there won't be any issues with needing to possibly change Excel's security settings) and big bonus - it's a "live" solution - meaning it's flagging up errors as they're made (assuming you issue the workbook to the lecturers with the change already implemented).
That all having been said, I'm still more than happy for us to implement my solution, too. I suggest implementing Eric's, then if you still want to have a code-based solution, we'll do mine too - on another copy of your workbook (you should really always experiment on a copy book of your treasured work, in case things go awry - PARTICULARLY with code-based solutions). WARNING - if you enjoy working with the code, you may get hooked forever!!

We'll wait to hear back from you.

 

[eskimo85]

Hi sykes

Thank you, I tend to be a bit verbose when writing anything, well if I'm to be honest; in any form of communication :D, so in this instance being that way has proven to be an advantage rather than an annoyance it seems ... I had hoped that somewhere amongst my many words someone would be able to find what they needed, so I'm thrilled, sincerely. Note to self though Sue, learn to be succinct lol

Do you know sykes, I was thinking the same thing. Our work laptops are very tightly restricted by the IT Administrators and I suspect I may not have even been able to alter the security settings to let the macro run. However I could have put in a request to have them do so if I needed to, so if needed that won't be a problem I don't think.

I'm going to have a crack on Eric's solution today at home using a dummy spreadsheet, and that way once I have it working I can take the example with me into work to implement on the actual sheets which are due, as it turns out, to be downloaded and checked on Thursday and Friday of this coming week. The lecturers input the grades and marks into a software program, our Exams office then extrapolates the data and saves same in .csv format, and I then download from the Exams' secure online repository. Talk about long winded - there's got to be a better way in my humble opinion. However I've not long been there and as such am just following the process. I then need to alter to .xlxs to do any required formatting (eg adding a leading 0 to any student ID that only has 5 digits rather than 6 in their ID) before converting back to .csv to upload back to the Exams office's portal once they've been checked and amended. Hoochey mama, lol.

Hope you all have a wonderful day, and I'll be back in touch again soon. A huge thank you to you all.

Sue

 

[Eric W]

Good luck with your testing. Let us know if you have any questions. That's the thing about Excel, there's always 10 ways to do a given task!

One other thought about your uploading. When you convert an Excel file to .csv, it takes the cell value as displayed. So if you have a custom number format on the column with the student IDs that has 6 digits, you might be able to save a little work there too.

Good luck!

 

[eskimo85]

Oh Eric, I swear I could marry you . Have just done a quick test on a dummy sheet and it worked instantly and perfectly - as if there was any doubt that it would!! I even amended some of the intentional erroneous figures in D to correct ones and as anticipated the background colour vanished. Oh how wonderful. You have no idea how happy I am - I swear that this is going to save me hours and hours of work, time that I really don't have spare. If only there was something I could give you, and sykes and tyija1995 in thanks. I take my hat off to you wonderful people for giving of your own time to assist others, such an increasingly rare things these days. Please know that I am filled with gratitude and thank you all immensely. Will certainly be utilising on Thursday and Friday and from thereon in.

Warmest regards, Sue