Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    More than 7 Nested Ifs (Excel 2002)

    Hi

    We are frequently advised we cannot use more than 7 IFs, which is normally so, However on searching various websites I found this little gem, there are probably better ways of doing things, but if you want to go just over the seven this would be ideal (please see attached sheet). In this sheet it uses Thirteen.

    I hasten to point out this not my own doing. If I remember where I found it I will post it.

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More than 7 Nested Ifs (Excel 2002)

    Thanx Braddy. Very interesting! Thanx for the idea.

    I speak for myself though....
    I'd rather use VLookup and a reference table before I do it this way though! (Much to much to check if modifications are needed!)

    Cheers
    Regards,
    Rudi

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: More than 7 Nested Ifs (Excel 2002)

    Hi Rudi

    Thanks for your input, I just though it might be useful for simpler spreadsheets, the kind which people at my level create. But I agree with you in principle.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    4,989
    Thanks
    2
    Thanked 402 Times in 331 Posts

    Re: More than 7 Nested Ifs (Excel 2002)

    <hr>might be useful for simpler spreadsheets<hr>

    A spreadsheet that calls for 7 or more nested IFs is never going to be simple!

    Even so, the following formula would give the same result as the one in your spreadsheet, with no IFs, LOOKUPs etc, and no need for an intermediate cell to calculate the result:
    =($A$4=1)*11+($A$4=2)*22+($A$4=3)*33+($A$4=4)*44+( $A$4=5)*55+($A$4=6)*66+($A$4=7)*77+($A$4=8)*88+($A $4=9)*99+($A$4=10)*100+($A$4=11)*110+($A$4=12)*120 +($A$4=13)*130

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: More than 7 Nested Ifs (Excel 2002)

    HI Macropod

    I see what you mean, but then not everthing is simple until you know how to do it is it? <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than 7 Nested Ifs (Excel 2002)

    Of course the rather silly example in cell I4 and I5 can be done this way too:

    =IF(AND($A$4<14,$A$4=INT($A$4)),CHOOSE($A$4,10,20, 30,40,50,60,70,80,90,100,110,120,130),"Not Found")

    Or:

    =IF(AND($A$4<14,$A$4=INT($A$4)),$A$4*10,"Not Found")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: More than 7 Nested Ifs (Excel 2002)

    Hi Jan Karel

    It's begining to look as if I am too inexperienced to have made this post in the first place!.

    I will stick to asking for help in future. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than 7 Nested Ifs (Excel 2002)

    oo i never thought about that, good idea jan karel! <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than 7 Nested Ifs (Excel 2002)

    What you found works both for you and the person you found it from. Not everybody is like Rudi and can automatically think in VLOOKUPs.
    Gre

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    4,989
    Thanks
    2
    Thanked 402 Times in 331 Posts

    Re: More than 7 Nested Ifs (Excel 2002)

    Hi JK,

    Not quite - for $A$4<10 the multiplier is 11. Not like you to miss that.

    Taking my earlier response one step further:
    =(INT($A$4)<10)*(INT($A$4)>0)*INT($A$4)*11+(INT($A $4)>9)*INT($A$4)*(INT($A$4)<10)*10
    Of course, if the user can be certain that $A$4 will only ever hold integers, all the INT expressions in the above can be deleted.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More than 7 Nested Ifs (Excel 2002)

    Hi Braddy,
    <hr>It's begining to look as if I am too inexperienced to have made this post in the first place!.<hr>
    Its impossible to compare your brain with 10 million other brains out there! You will come up feeling like a dumbo 100% of the time!
    I've lots of times posted tips and ideas, thinking they were great, and then to discover a guru posting some other way that is 100 times better than my tip. The GREAT thing about this is that you learn and it opens your eyes to the flexibilities that Excel does provide.

    Never back away from posting anything. It always provides an answer for someone else, and provides you with new ideas from that which you posted.

    Lounge slogan : TODAY, TOMORROW, TOGETHER WE RULE!
    Regards,
    Rudi

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: More than 7 Nested Ifs (Excel 2002)

    The method you posted is valid. It may not be the best solution in all circumstances, but I'm sure it will be useful to some.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •