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

 

[eskimo85]

Lol, sounds divine. Hobart's pretty chilly at the moment, although if I had to pick I'd much rather cool weather than hot weather. I remember when I was in my teens and enjoying the sun a lot more than I do now, my dear old Nan said to me that she got married in London during a heatwave. I asked her how hot it was, thinking she'd say something like 39 degrees Celsius, and she said it would have been a least 28 degrees!! I nearly fell off my seat laughing. She then explained that the UK doesn't (well didn't back then) get the temperatures we over here swelter in and back in 1940 a run of days in the high twenties was most unusual. In fact I left Adelaide to move to Hobart for that very reason. Summers in South Australia are becoming horrendous with weeks on end 35+ each day. Give me a nice warm 23 degrees and I'm in heaven . Enjoy that bbq if you end up having a grill up.

Update re the Results process. It has all been completed without an error or hiccup - and in record time. I can't thank you all enough!!

Sue

 

[sykes]

I could easily manage 3.5, but 35's getting silly! Spent quite a bit of time in the Middle East, when I was flying, and endured 50 C a few times. That wasn't funny! Not even a titter...

Glad you're all sorted - I'm sure Eric'll be pleased to hear that, too. I've a feeling you'll be back for more in the not-too-distant future - now that you're the office Excel guru!!

 

[eskimo85]

Hi Eric, I cant believe it's been almost a year now that I've been using the wonderful solution you provided on my Results checking job at the Uni. It has been working an absolute treat and has cut the job down timewise so greatly it is hard to imagine why no-one before me ever thought of utilising Excel on it before. I can't thank you enough. Truly.

Just recently however we've had 3 new grades added into the mix which I've needed to incorporate into your formula solution - I've tried, without success, because it's all gobbledigook to me LOL. I wonder if you could lend a hand again please? You'll see in the initial posts that I give the example of columns C & D. In reality the spreadsheet concerned refers to P & Q so I just amended the formula accordingly. Like the grade 'AN' in the original query, I now have three others that the Q cell mustn't have a mark in (ie it should be an empty cell). The new ones are UP, NS, & WN. How would I go about adding them to the formula please? Also, I didn't take into account that there must always be something in each cell of the P column, and if there isn't then the conditional formatting would need to alert me to it in the Q column, like with all other errors, by highlighting the cell with the colour I choose. How could I incorporate this scenario into your wonderful formula?

Your assistance would again be greatly appreciated if you can spare the time?

Sincerest thanks, Sue

 

[Eric W]

Hi, Sue!

Good to hear from you again. Nice to see that the CF formula is still working for you! I've found that few people actually look for improvements. As long as what they're doing works, they'll continue to use it, even when it can be improved.

I'm a little miffed you called my formula gobbledigook! I think it's a wonderful formula! It shouldn't be too hard to change. Here's the original:

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

and here's how to update it:

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

You just need to add the new grades to the list, with a period separating them, and then add a condition for each one which shows the acceptable values. Bit I do see a problem!

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

You already had NS as a grade with a different acceptance criterion. Did you have a typo with the new grade, or is the old version not used anymore?

As far as highlighting the Q column if the P column is empty, I'd use an additional rule instead of making a complicated formula even more so. After you enter that formula, select the Q column again, click Conditional Formatting > New Rule > Use a Formula > and enter

=P1=""

You can then pick a color, maybe something different than the original.


How's the weather there?! You must be in the middle of winter. We're starting summer, with 33 degrees today. And the hottest months are still to come! Hope you're doing well!

-Eric

 

[eskimo85]

Hi Eric

Thanks an absolute million - bless your cotton socks!!

My apologies that the gobbledigook comment didn't come across as intended - I was actually admitting to my absolute ignorance at how to put anything but a very basic formula together, certainly be no means reflecting your magnificent one, which has saved me an indescribable amount of work, and negating the chance of human error when visually checking. Although perhaps you were joking given the smiley face, lol.

You deadset are an absolute champ. Not only have you assisted again, but this time, through your great explanation, I have understood the formula that bit more. I had tried to do it myself by just adding them in with the period between them, but didn't add the extra Q1s at the end. I am so grateful. You'll make me a formula guru I'm sure of it (in 50 years lol).

Thanks for picking up on the NS duplication, I might have to follow up on that one when I return to work tomorrow to make sure I have the correct interpretation of the NS grade, just to make sure. Perhaps that one has recently changed. I admit, I hadn't realised it was already in the formula because it highlighted a number of times when I went through the Semester 1 grades yesterday so I just assumed it wasn't because it was the first time in year that it has.

Great idea re CF for =P1="", makes a lot of sense, and is just as easy to do following the other one. Ta

I really honestly can't thank you enough Eric, you're such a gem.

Yep, we're well and truly into winter now is Tasmania, although the worst of the winter months is usually the last one, August. We didn't have much of a summer this year which is unusual because they're usually glorious, and this winter looks like it's going to be a particularly cold one. Oooh 33 degrees sounds just lovely at the moment - I'd love to thaw out lol. I think our top today is only meant to be 9 degrees C.

Will let you know about the NS discrepancy once I've reviewed the updated grade conditions tomorrow (hopefully, if the day doesn't go pear shaped).

Toodles for now

Sue

 

[Eric W]

Oh, don't worry about the gobbledigook comment! I was just teasing you! I couldn't find a "mock indignation" emoji though. I am glad it seems to work for you, let me know what you find out about the NS issue. Have a great day!

-Eric

 

[eskimo85]

Hee hee, I thought you may have been - oooh I love the idea of a 'mock indignation' emoji. Let's patent it now lol.

Well, have been in touch with the Exams office (aka those who know all, and who must be obeyed), and the gospel according to them is as follows:

Q P

NN 0-49
PP 50-59
CR 60-69
DN 70-79
HD 80-100

UP cell must be blank
AN cell must be blank
WT cell must be blank
WN cell must be blank, and
NS cell must be blank

So obviously until this recent run through I haven't had any NS grades as it hasn't ever come up as an issue before.

I might see if I can get some time on the weekend to have a fiddle with your great original formula, as my next lot of Results will be coming through next week. No rest for the wicked as they say

Thanks so much again Eric

 

[eskimo85]

Ok, just had an early lunch break as I wanted to update it while it was all still fresh in my head. Would you mind casting your eyes over it Eric to make sure I've done it correctly please? I've updated to Q2 and P2 throughout as that's where the data commences (s/sheet has a header row). I'm assuming that's the right thing to do?

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

and

=P2=""

Ta muchly

 

[Eric W]

Ok, just had an early lunch break as I wanted to update it while it was all still fresh in my head. Would you mind casting your eyes over it Eric to make sure I've done it correctly please? I've updated to Q2 and P2 throughout as that's where the data commences (s/sheet has a header row). I'm assuming that's the right thing to do?

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

and

=P2=""

Ta muchly

Almost! When you took out the old NS code, you also needed to take out the condition it was checking, which is the ISNUMBER condition. So it should be:

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

If you want to allow for a header row, make sure that you don't select that row when entering the formula. Select Q2 down to your maximum row.



Now I looked at that formula, and I saw 5 instances of Q2="", and usually when there's a lot of duplication, there's a way to combine them to make a simpler formula. I came up with a formula that's less than half the size of this one, and I think a little easier to understand, but it requires making some lists somewhere, either on the sheet, or as a Name value. I hesitate a bit to show it, since I don't want to make things more confusing, and you have something that works and you're used to. But if you want, let me know and I'll explain that one.

-Eric

P.S. You're sure causing me to miss sleep! It's after midnight here, and I have to go to work tomorrow!

 

[pjmorris]

Can I propose an additional step. In the template that the teachers use to produce your spreadsheets include a 'custom' data validation in column D using this formula (Which is Eric's formula without the initial Not as we're using it here to prove something is correct).

=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)

While I would still check what they've provided you this should stop any errors occurring and mean you don't have to go back to them to check. If you could live without NS and WT or AN then you could use the value in column D to simply set the value in C according to the grade.

HTH