1

I am writing a Google Sheet that checks a student's year level, then assigns a letter grade based on their mark in an assessment. If there is no mark, it is designed to leave the cell blank.

The following formula is successful, if a bit clunky. Is there a more elegant solution to this problem?

=IF(COUNTBLANK(AA12)>=1,"",IFS(P12=5,(IF(AA12>32,"A", (IF(AA12>28,"B",(IF(AA12>19, "C", (IF(AA12>16, "D", "E")))))))),P12=6,(IF(AA12>37,"A", (IF(AA12>32,"B",(IF(AA12>24, "C", (IF(AA12>19, "D", "E")))))))),P12=4,(IF(AA12>28,"A", (IF(AA12>24,"B",(IF(AA12>16, "C", (IF(AA12>12, "D", "E")))))))),P12=3,(IF(AA12>23,"A", (IF(AA12>19,"B",(IF(AA12>12, "C", (IF(AA12>8, "D", "E"))))))))))

The students grade is stored in Column 'P', and their mark in Column "AA"

Any suggestions are welcome.

1 Answer 1

0

link to my sheet.

I basically used VLOOKUP on its true state. According to the Documentation:

If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned.

So we have 4 lookup tables, one for each grade. I used named ranges to reference each easily. I had to add one to each grade since VLOOKUP will look for less than or equal, while your formula only less than. Easy enough fix.

Each lookup table also has to be sorted (lowest to highest). This is important. You might as well just copy and paste my Data spreadsheet onto yours to keep it consistent.

The main formula uses just uses SWITCH to differentiate between grades. You're going to need to adjust the references a bit, but that's simple enough. Here's an adjusted formula:

=SWITCH(
    P12,
    3,
    VLOOKUP(
        AA12,
        grade3LookUp,
        2
    ),
    4,
    VLOOKUP(
        AA12,
        grade4LookUp,
        2
    ),
    5,
    VLOOKUP(
        AA12,
        grade5LookUp,
        2
    ),
    6,
    VLOOKUP(
        AA12,
        grade6LookUp,
        2
    )
)

It's a bit more work, but it's definitely much simpler and easier to maintain. Also easier to apply to other grades.

P.S. My sheet has a button on the right. It so that I can easily trigger the random function and see if it's behaving correctly. Thought I should clarify.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.