Need Help with Nested If Statements

[TKrepitch]

Hi, everyone. I think this is a simple question.

This formula works:

=IF(AND(ISBLANK(B5),ISBLANK(W5)),"Pending",IF(AND(B5="*"),ISBLANK(W5),"Installing"))

This formula does not:

=IF(AND(ISBLANK(B5),ISBLANK(W5)),"Pending",IF(AND(B5="*"),ISBLANK(W5),"Installing",IF(AND(B5="*",W5="*","Installed")))

I seem to be doing something wrong when I add in the third combination
at the end.

Basically, what I am trying to get is this:

If B5 and W5 and both blank, then the cell should say "Pending".
If B5 has anything in it and W5 is blank, then the cell should say
"Installing".
If B5 and W5 both have something in them, then the cell should say
"Installed."

Thanks for your help!

 

[Bob Phillips]

=IF(AND(ISBLANK(B5),ISBLANK(W5)),"Pending",
IF(AND(B5<>"",ISBLANK(W5)),"Installing",
IF(AND(B5<>"",W5<>""),"Installed","")))


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[joeu2004]

Basically, what I am trying to get is this:
If B5 and W5 and both blank, then the cell should say "Pending".
If B5 has anything in it and W5 is blank, then the cell should say
"Installing".
If B5 and W5 both have something in them, then the cell should say
"Installed."

First, you do not cover all the cases. What if B5 is blank, but
W5 has something in it? I suspect you want the result to be
"Pending" or blank.

The simplest formula relies on the left-to-right processing of
IF() parameters to avoid unnecessary AND() and OR() usage.

One of the following should work, depending on your answer
to the question above.

=if(B5="", "Pending", if(W5="", "Installing", "Installed"))

=if(and(B5="",W5=""), "Pending",
if(B5="", "", if(W5="", "Installing", "Installed")))

These work because in the first case, for example, W5="" is
implicitly and(B5<>"",W5="") because the first test, B5="",
is false (ergo B5<>"" is true) if we are relying on the false-part
of the IF() function.

Note: B5="" will be true even if B5 contains a formula that
returns the null string, whereas isblank(B5) would be false in
that case. Neither test condition covers the case where B5
appears to be blank, but actually contains one or more blank
characters (e.g. =" ").

This formula works:
=IF(AND(ISBLANK(B5),ISBLANK(W5)),"Pending",IF(AND(B5="*"),
ISBLANK(W5),"Installing"))

If it worked for you, I suspect it was only by coincidence. See below.

This formula does not:
=IF(AND(ISBLANK(B5),ISBLANK(W5)),"Pending",IF(AND(B5="*"),
ISBLANK(W5),"Installing",IF(AND(B5="*",W5="*","Installed")))

The parentheses are in the wrong place for the latter AND()
functions. This is not rocket science: the parentheses should
encompass all of the function parameters. I suspect you want
(simply correcting your text without checking your logic):

=if(and(isblank(B5),isblank(W5), "Pending",
if(and(B5="*", isblank(W5)), "Installing",
if(and(B5="*",W5="*"), "Installed", "")))

Note that I added a false-part ("") in the last case. It is rarely
desirable to omit the false-part, since if it applies (i.e. the
if-condition is false), the result will be the word FALSE.

 

[TKrepitch]

I couldn't get this to work, unfortunately. I need a class on nested
if statements - I am awful with them.

 

[TKrepitch]

Wow. I really appreciate this. The reason I had it set up the way I
did was because W5 will never be filled unless B5 is already filled,
but you are right that it is better to set it up the way you did. Your
more elegant solution works beautifully and I thank you very much for
it. I hope you have a great weekend. Thanks again!

 

[joeu2004]

I couldn't get this to work, unfortunately. I need a class on
nested if statements - I am awful with them.

I am curious why it did not work, since it looks syntactically correct
to me off-hand. Do you mean that it failed to give the desired result?
Or do you mean that you got syntax errors when you entered it?

In either case, I suspect the issue is a simple matter of balancing
parentheses correctly. That is always a problem -- for everyone --
despite the help that Excel tries to provide both by color-coding
balancing parentheses and by bouncing the cursor back and forth,
which I dislike.

My "solution" is to type the balancing right parenthesis immediately
after the left parenthesis, then using left-cursor to back up and fill
in
the parameters (or subexpression). It is a little cumbersome; and it
is not ironclad because it is easy to "swallow" a parenthesis
inadvertently
while editing using Backspace and Delete. But it works 90% of the
time.

For you, the other important thing might be to stop looking at these
as "statements", as they are in other programming languages, and
recognize them as functions. That should reinforce the idea of
encapsulating all of the components of the "statement" -- that is,
all of the parameters of the function -- between the function
parentheses.
With the IF() function, there is conditional parameter, a true-part
parameter, and a false-part parameter.

Once you master the art of balancing parentheses -- a high goal
that none of us truly achieves because "sh*t happens" -- I am sure
you will no longer be confused by "nested functions" because there
simply is nothing to be confused about; that is, there is no special
concept to master.

PS: The only "nesting concept" to master is the limitation on the
number of nested functions, at least for IF(). Just be aware of it.
With IF() functions, if you hit that nesting limitation, it is usually
because there is a better way to accomplish the same thing anyway.
Isn't it nice that Big Brother is watching over you and prevents you
from writing "poor" formulas? ;-)