Nested IF Statements in Excel Formula

Question

So, to preface, I'm a complete novice at this Excel business. I've found similar examples via Google of how to do this, so I don't think I'm too far off:

=IF( AND(D6=FALSE, OR(ISBLANK(B10),B10=0) ),IF( AND(D6=TRUE,B10>=1)," ","Enter number of components"),"fail")

Essentially, the first IF block evaluates the contents of the AND expression. If that condition passes, I want to evaluate the second IF block (which will echo an error to the cell if the condition fails).

Thanks in advance for your assistance :)

Answer 1

With something this complex, you should try breaking it up into smaller functions in each cell. A good candidate would be to move the AND into its own cell, and then use that as the first argument of AND. I'm not sure if you can have an IF as a result, so try setting the value_if_true and value_if_false to other cells, and do the individual calculations there.

Answer 2

according to your code, you are first evaluating if D6=FALSE, when that comes true, you then are trying to evaluate if D6=TRUE. Well from the first evaluation you already know it's false so your logic is flawed.

it seems to me that you may just want to evaluate once

=IF( AND(D6=FALSE, OR(ISBLANK(B10),B10=0) ), "Enter number of components", "Fail")

Answer 3

First, let's look at the code's structure:

if D6 = false AND (isblank(B10) OR B10 = 0)
  if D6 = true AND B10 >= 1
    put " "
  else
    put "Enter number of components"
else
  put "fail"

One problem you'll find is that every time you enter the inner IF statement, D6 is FALSE. This means D6=TRUE always results in false, so the second IF statement always fails. In other words, your code is equivalent to:

=IF(AND(D6=TRUE, OR(ISBLANK(B10), B10=0)), "Enter number of components", "fail")

Is that not what you want?

Answer 4

I broke down the logic to make it clearer what is going on in the formula. Maybe this will help you piece together how the IF statements work.

if (D6 is false & B10 is blank)
     then if (D6 is true & B10 >= 1)
          then "" THIS WILL NEVER HAPPEN
          else "Enter number of components"
else "fail"

Answer 5

Turns out this got the job done:

=IF( OR( AND(D6=TRUE,  OR(ISBLANK(B10),B10=0)), AND(D6=FALSE, OR(B10>=1))), "Selection Invalid","")

It appears that trying to nest the second IF within the "THEN" block of the first IF made excel angry. The above works, thanks for the responses!