Tags: excel, false, formula, formulas, logical_test, microsoft, msdn, nested-if, returned, software, value-if-true

Nested-IF formulas

On Microsoft » Microsoft Excel

5,625 words with 4 Comments; publish: Thu, 22 May 2008 08:00:00 GMT; (306111.33, « »)

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!)

All Comments

Leave a comment...

  • 4 Comments
    • 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

      A hint to posters: Specific, detailed questions are more likely to be

      answered than questions that provide no detail about your problem.

      "GARY" wrote:

      > 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!)

      >

      #1; Thu, 22 May 2008 08:02:00 GMT
    • 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

      On Mar 23, 7:15 pm, "GARY" <gcott....excel.todaysummary.com.co.riverside.ca.us> wrote:

      > 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!)

      #2; Thu, 22 May 2008 08:03:00 GMT
    • 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

      #3; Thu, 22 May 2008 08:04:00 GMT
    • 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

      "nhebb" wrote:

      > 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

      >

      #4; Thu, 22 May 2008 08:05:00 GMT