Wondering whether a formula could assist or is even possible ...
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

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

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    Hobart, Tasmania
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    588
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    Hey,

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

    Code:
    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)
    Last edited by tyija1995; Jul 12th, 2019 at 06:32 AM.
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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!!
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    New Member
    Join Date
    Jul 2019
    Location
    Hobart, Tasmania
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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.

  5. #5
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,320
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  6. #6
    New Member
    Join Date
    Jul 2019
    Location
    Hobart, Tasmania
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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

  7. #7
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  8. #8
    New Member
    Join Date
    Jul 2019
    Location
    Hobart, Tasmania
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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

  9. #9
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,320
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  10. #10
    New Member
    Join Date
    Jul 2019
    Location
    Hobart, Tasmania
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •