Help with multiple IFs and/or ISBLANK

Question

Rocket-JoJo Author

Level 1

11 points

Help with multiple IFs and/or ISBLANK

I need help finding the error in my formula:

IF(AND(B2≠""),IF(AND(OR(ISBLANK(E2),ISBLANK(F2),ISBLANK(I2)),"Check Data”,"N/A")))

I'm trying to:

test B2 to see if it's null

if B2 is null do nothing

if B2 is not null, then

check to see if either E2, F2 or I2 are blank

if all (or either are blank) return “Check Data”

if false return “N/A”

I'm getting an error: IF requires between 2 and 3 arguments, but only given 1

I don't have a lot of experience using multiple criteria in an IF statement. I reviewed various post from the community to develop the formula, but obviously I overlooked something. But, it appears to me I have two arguments (check B2 then check E2, F2 and I2. I guess I'm having a hard time breaking-down (or identifying) the different argument (parts) of the formula.

In addition the solution (correction) to my formula, I really want to understand what I did wrong and the best technique to look at the parts of a formula to find errors in the future. I clicked on various parts of my formula to verify I had things like opening and closing parentheses placed correctly, but I could not isolate my error.

Thanks

Mac mini 2018 or later

Posted on Jan 24, 2022 9:48 PM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,023 points

Jan 25, 2022 12:40 AM in response to Rocket-JoJo

A quick parsing of the first IF in your formula. Pairs of opening and closing parentheses have been set to matching colours. My quick look indicated two arguments for the first IF.

What is the purpose of AND in this? The first AND has only a single condition to test.if it is true (B2 contains something other than a null string) the second IF is called.

In the second IF, there is one argument—everything after the opening parenthesis of that IF and its closing parenthesis is enclosed y the opening and closing oarentheses foe the second AND.

Here's a copy of the table with a different formula in column A, and some 'data' filled in. The pale yellow colour fill in some cells was added manually to mark the cells checked for data by the formula.

IF(B2="","",IF(OR(ISBLANK(E2),ISBLANK(F2),ISBLANK(I2)),"Check Data","N/A"))

Regards,

Barry

Reply

Answer 2

SGIII

Level 8

37,550 points

Jan 25, 2022 6:34 AM in response to Rocket-JoJo

Rocket-JoJo wrote:

I guess I'm having a hard time breaking-down (or identifying) the different argument (parts) of the formula.

You are lot alone! It is really easy to get lost in a tangle of "nested ifs". It's such a common problem that modern spreadsheets have introduced the more modern IFS function to ease the burden.

So you can now do something like this.

=IFS(B="","",LEN(E2&F2&I2)<3,"Check Data",TRUE,"NA")

The IFS structure tends to be much simpler. No nesting with all those parentheses to worry about!

IFS(ifs-expression, ifs-true, ifs-expression…, ifs-true…)

So in the example above:

IFS(B2 is blank, "", E2 or F2 or I2 are blank, "Check data", otherwise put "N/A")

More on IFS here with examples.

SG

Reply

Answer 3

Rocket-JoJo Author

Level 1

11 points

Jan 25, 2022 7:58 AM in response to Barry

Barry,

Thanks for your help, explanation and the parsing of the IFs . It's certainly beneficial.

As far as your question "What is the purpose of AND in this?" I honestly don't know. My experience with Numbers is reading the function descriptions in the help section, reviewing the Numbers' User Guide and coming to this site to search for help. I find something I think meets my needs, then I start experimenting. Often, I find exactly what I need. But there are times I feel overwhelmed when I start combing statements.

I've searched online for a Numbers training courses (I don't mind paying for the course), but they appear to spend a lot of time on sheet formatting, inserting images, etc. and only address basis formulas. Any suggestions are appropriated.

Reply

Answer 4

Rocket-JoJo Author

Level 1

11 points

Jan 25, 2022 8:09 AM in response to SGIII

SGill,

Thank you. I was not familiar with the IFS or the LEN. And, I certainly could not have combined them. I have a very limited knowledge beyond basic functions (e.g., add, subtract, etc.). I've printed the page you linked and will review.

Your solution worked for me as well. Wish I could mark both yours and Barry's answered as solved.

I'm looking for an online Numbers training course that would go into depth (advanced) on formulas. Any suggestions?

Reply