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

eskimo85

New Member
Joined
Jul 12, 2019
Messages
16
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 :)
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,649
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 12, 2019
Messages
16
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,780
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! :giggle: 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

New Member
Joined
Jul 12, 2019
Messages
16
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,780
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

New Member
Joined
Jul 12, 2019
Messages
16
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

New Member
Joined
Jul 12, 2019
Messages
16
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,780
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! :sleep:
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,848
Office Version
2016
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,708
Messages
5,470,324
Members
406,690
Latest member
Yaron Yehezkel

This Week's Hot Topics

Top