+ Reply to Thread
Results 1 to 11 of 11

Shorten nested formula

  1. #1
    Registered User
    Join Date
    05-28-2009
    Location
    Barbados
    MS-Off Ver
    Excel 2007
    Posts
    8

    Unhappy Shorten nested formula

    I wrote a nesting formula but Excel 2007 has limited the number of nesting functions that I can use. Apparently it only limits you to six or seven.

    =IF((AND(G22="R",K22="Y",M22="Y")),(H22*L22)*20%*N22,IF((AND(G22="R",K22="Y")),(H22*L22)*20%,IF((AND(G22="R",M22="Y")),(H22+N22)*20%,IF((AND(G22="R",K22="N",M22="N")),H22*20%,IF((AND(G22="S",K22="Y")),(H22*L22)*20%,IF((AND(G22="S",K22="N")),(H22*I22)*20%,IF((AND(G22="T",J22="",K22="Y",M22="N")),(H22*L22)*20%,IF((AND(G22="T",K22="N")),(H22*J22)*20%,"")))))))

    Can anyone tell me how I can shorten this!
    Last edited by sbro; 05-29-2009 at 09:34 AM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    re: Shorten nested formula

    Hi,

    in Excel 2003 you can only nest 7 IF statements, in Excel 2007 you can nest more (but I don't know how many). Also, maybe when you're working in compatibility mode, Excel 2007 may not allow funcitonality that is not compatible with 2003, hence the error.

    What you can do to shorten your formula is look at which statements lead to the same calculation and then group them in the same IF statement rather than individually.

    In your formula I see that the same calculation

    (H22*L22)*20%

    is carried out for three of your conditions, i.e. the second, fifth, and seventh condition

    IF(AND(G22="R",K22="Y"),(H22*L22)*20%, ...
    IF(AND(G22="S",K22="Y"),(H22*L22)*20%, ...
    IF(AND(G22="T",J22="",K22="Y",M22="N"),(H22 *L22)*20%,

    You could group these like this:

    IF(OR(AND(G22="R",K22="Y"),AND(G22="S",K22="Y"),AND(G22="T",J22="",K22="Y",M22="N")),(H22*L22)*20%,0), ...

    which means your formula will only have 6 nested IF statements instead of 8

    cheers

  3. #3
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Shorten nested formula

    By using below formula you can use more then 20 IF's .... just paste this code and see it works or not ......

    =IF(OR(AND(G22="R",K22="Y",M22="Y"),AND(G22="R",K22="Y"),AND(G22="R",M22="Y"),AND(G22="R",K22="N",M22="N"),AND(G22="S",K22="Y"),AND(G22="S",K22="N")),IF((AND(G22="R",K22="Y",M22="Y")),(H22*L22)*20%*N22,IF((AND(G22="R",K22="Y")),(H22*L22)*20%,IF((AND(G22="R",M22="Y")),(H22+N22)*20%,IF((AND(G22="R",K22="N",M22="N")),H22*20%,IF((AND(G22="S",K22="Y")),(H22*L22)*20%,IF((AND(G22="S",K22="N")),(H22*I22)*20%,"")))))),IF(OR(AND(G22="T",J22="",K22="Y",M22="N"),AND(G22="T",K22="N")),IF((AND(G22="T",J22="",K22="Y",M22="N")),(H22*L22)*20%,IF((AND(G22="T",K22="N")),(H22*J22)*20%,""))))
    If this post helps, Please don't 4get to click the star icon located at the bottom left of my Post.

  4. #4
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    36 IF's

    The Formula is just to show that how we can use more then 7 IF's. In this sheet we are using 36 IF's which is fulfilling 37 conditions.

    Put this formula in Cell B3 and in A3 Cell enter any alphabet from A-Z and Numeric 0-9 , It will give you the number.

    =IF(OR(A3="a",A3="b",A3="c",A3="d",A3="e",A3="f",A3="g",A3="h"),IF(A3="a",1,IF(A3="b",2,IF(A3="c",3,IF(A3="d",4,IF(A3="e",5,IF(A3="f",6,IF(A3="g",7,8))))))),IF(OR(A3="i",A3="j",A3="k",A3="l",A3="m",A3="n",A3="o"),IF(A3="i",9,IF(A3="j",10,IF(A3="k",11,IF(A3="l",12,IF(A3="m",13,IF(A3="n",14,15)))))),IF(OR(A3="p",A3="q",A3="r",A3="s",A3="t",A3="u"),IF(A3="p",16,IF(A3="q",17,IF(A3="r",18,IF(A3="s",19,IF(A3="t",20,21))))),IF(OR(A3="v",A3="w",A3="x",A3="y",A3="z"),IF(A3="v",22,IF(A3="w",23,IF(A3="x",24,IF(A3="y",25,26)))),IF(OR(A3=1,A3=2,A3=3,A3=4),IF(A3=1,"N1",IF(A3=2,"N2",IF(A3=3,"N3","N4"))),IF(OR(A3=5,A3=6,A3=7),IF(A3=5,"N5",IF(A3=6,"N6","N7")),IF(OR(A3=8,A3=9),IF(A3=8,"N8","N9"),IF(A3=0,"Zero","Non of Alphabet / Numerics"))))))))
    P.S. I know there are many ways to do this but I am only focusing to use of IF’s, as in many cases we don’t have any choice except to use IF’s.
    Attached Files Attached Files
    Last edited by mubashir aziz; 05-29-2009 at 06:07 AM.

  5. #5
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Shorten nested formula

    The limitation in excel is not the number of IF's its the nesting of ifs which is limited to 7!
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  6. #6
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Shorten nested formula

    @thx sq for the clarification but this info is only for new users because I faced the same problem in my early days of education and always struck to use of nested IF's ........

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Shorten nested formula

    If we assume the ordering of the tests in your original is important (ie we can't group tests together based on common outcome) then I think perhaps the below will work:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-28-2009
    Location
    Barbados
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Shorten nested formula

    Thanks Everyone for all of the help! Your answers were great
    I did what Teylyn suggested and changed the spreadsheet from compatibility mode and Excel allowed the formula!
    In case it gives me trouble in the future I will keep all of your suggestions for future reference.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Shorten nested formula

    Glad you have resolved - just bear in mind the formula won't work if opened in an XL version preceding XL2007 (and that the formula can be replaced with one that will / or at least should do )

    Please remember to mark thread as SOLVED - see FAQ / How To ? for further details if unsure.

  10. #10
    Registered User
    Join Date
    05-28-2009
    Location
    Barbados
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Shorten nested formula

    lol Thanks i will keep that in mind I saved all of the suggestions just in case that happens.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1