Nested-IF formulas

[GARY]

In a simple "IF" formula:

if the logical_test is TRUE, the 'value-if-true' is returned
if the logical_test is FALSE, the 'value-if-false' is returned.

(I can easily follow that).


====================

But when writing nested-IF formulas (with multiple logical_tests), I
frequently get "lost" determining which of the multiple 'value-if-
true" statements and which of the multiple 'value-if-false" statements
go with each of the multiple logical_tests.

Can someone please un-confuse me? (A visual diagram would be great!)

 

[Dave F]

Nested IFs follow this syntax: IF THEN ELSE IF THEN ELSE

IF true THEN action ELSE IF true THEN action ELSE ....

E.g.: =IF(A1=A2,A3,IF(A2=A4,A5,A6))

Dave

 

[Pete_UK]

I can't post a diagram here, but in flowchart symbols you can think of
an IF .. THEN .. ELSE .. ENDIF as starting with a diamond shape in
which a question is asked (or the condition is stated) and then coming
out of the left hand corner is "YES" or "TRUE" and out of the right
hand corner is "NO" or "FALSE". These arms then move down into a box -
Action_if_true or Action_if_false and then beyond these boxes the two
outputs join up to form the ENDIF point.

This control structure looks rather like a box with a diamond on the
top line (flow being into the top of the diamond), an action box is on
each of the vertical lines, and the bottom line has another line from
it in the centre taking the flow downwards.

This structure can replace either or both of the action boxes in the
vertical lines, so that you can have a nested IF on the TRUE side as
well as the FALSE side. The IF structure could then be used to further
substitute one or more of the Action boxes.

It's easier to follow with a diagram, but I hope this description
helps.

Pete

 

[nhebb]

Every diagram is going to be different, of course, depending on the
logic of the statements. If I get a really hairy one that I have
trouble getting the syntax right, then I create each "piece" of the
statement in separate cells. Then I go through and build up the final
statement from the inside out, using the formulas from each of the
cells. I hope that doesn't sound too confusing.

I think someone makes an Excel add-in for creating complex nested
IF's. You could always search around for it.

Also, there is a limit to the nesting. You can only go 7 nests deep.
Chip Pearson has an article showing how to work around this:
http://www.cpearson.com/excel/nested.htm

HTH,

Nicholas Hebb
BreezeTree Software
http://www.breezetree.com

 

[BoniM]

Just FYI - in 2007:
Nested levels of functions limit is 64
Length of formula contents limit is 8,192 characters
Others:
http://office.microsoft.com/en-us/excel/HP100738491033.aspx