Nested If Statements - If true vs. If false

[emboyce]

Hi all,

I am reviewing a workbook made by someone else and I have come across a formula syntax that I've not seen before. I'm hoping someone can explain to me what it is doing and if it is ok or not.

I use IF statements all the time and am familiar with their structure =IF(logical_test, [value_if_true], [value_if_false]). I am also familiar with nesting multiple IF statements, but I have only ever nested additional IF statements in the "value if false" argument.

Question: Is there ever a reason to nest IF statements in the "value if true" argument? How does Excel read that - like an AND, or like an OR? Excel isn't rejecting the formula and it is generating the intended result, but I don't understand why.

Specifics: The formula I am looking at checks to see that two cells (E5 and F5) are retrieving data and then returns either their sum or a message to "Enter All Information"

=IF(E5="Enter All Information", IF(F5="Enter All Information", "Enter All Information"), SUM(E5:F5))

Had I written the formula, I would have used =IF(OR(E5="Enter All Information", F5="Enter All Information"), "Enter All Information", SUM(E5:F5)). E5 and F5 will either have a number or show "Enter All Information" - no other value.

Thank you for your help!
E

 

[NARAYANK991]

Hi ,

Two IF statements one after the other is the equivalent of ANDing the two IF statements.

Your statement would therefore read :

IF E5 equals "Enter All Information" and F5 equals "Enter All Information" then
"Enter All Information"
Else
SUM(E5:F5)
EndIf

In the situation you have mentioned , using OR is correct , so that only when both E5 and F5 have numbers in them , then their SUM is taken.

Narayan

 

[emboyce]

Thank you, Narayan! Is there any reason to use one AND syntax over the other? Or is just one of the many things we can do more than one way?

 

[oldchippy]

Either way emboyce it's the same amount of characters

 

[NARAYANK991]

Hi ,

The difference surfaces when you deal with arrays !

See the uploaded file , where 3 formulae have been put in , and the data is in the form of arrays ; it is only the first formula which works correctly. Check this by changing the As to Bs.

The first IF statement will generate a 1 only when both the corresponding cells contain A ; both the formula using AND and the one using OR do not give the correct results , the reason being that they are not operating on the arrays of data , despite being entered as array formulae , using CTRL SHIFT ENTER.

Narayan