An IF(ISBLANK question

Question

Level 1

8 points

An IF(ISBLANK question

I have a spreadsheet I use to track my sons school grades (we homeschool) but have the problem that the cell where the overall daily grade sits, requires that there be something in all the cells or it gives me the RED TRIANGLE of death. Here’s the entry used.

IF(J160=15,"A",IF(AND(J160>12.667,J160<15),"B",IF(AND(J160>10.334,J160<12.65),"C",IF(AND(J160>8.001,J160<10.334),"D",IF(AND(J160>5.668,J160<8.001),"E",IF(J160<5.668,"F","000"))))))

And an image of the cells in question.

I thought of using the ISBLANK function but apparently not correctly. It should be all one line, not divided.

IF(ISBLANK(G174:G178),"--—",IF(J171=15,"A",IF(AND(J171>12.667,J171<15),"B",IF(AND(J171>10.334,J171<12.65),"C",IF(AND(J171>8.001,J171<10.334),"D",IF(AND(J171>5.668,J171<8.001),"E",IF(J171<5.668,"F","000"))))))))

It works with the score column, but not the total grade.

IF(ISBLANK(G174),"-----",(H174−I174)÷H174)

iPad, iPadOS 16

Posted on Nov 20, 2023 6:49 AM

Reply

Answer 1

Recycleur

Level 4

1,633 points

Nov 20, 2023 8:07 AM in response to Algoroth

There is some missing information to conclude, but here are some starters.

Your first formula could be simplified to that by using IFS instead of nested IF.

IFS(J160=15,"A",J160>12.667,"B",J160>10.334,"C",J160>8.001,"D",J160≤8,"E")

Then, ISBLANK only works on a single cell. If you put a range as argument it will only use the first cell.

Third, your second formula (in J169 I believe) refers to J171 which itself contains an error, so obvioulsy the result will also be an error. What is the formula in J171?

It would help to see the row and column IDs, or at least have them described.

Reply

Answer 2

SGIII

Level 8

37,532 points

Nov 20, 2023 8:34 AM in response to Algoroth

I find something like the following a little easer to keep the grade cutoff points straight, without getting tangled up the IF logic. You simply list your grade cutoffs between one set of { } and the grades between the next { }, separated within the { } by ; and do a straightforward lookup.

=XLOOKUP(B2,{15;12.667;10.334;8.001},{"A";"B";"C";"D"},"NF",-1)

It gets a little cumbersome in Numbers to have big tables where you have to refer to a cell J160 or J171. You might consider breaking things down into smaller tables on the same sheet. That's easier to navigate.

SG

Reply