Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2007
    Posts
    6

    Unanswered: Nested nested function

    Hi,

    I'm trying to get excel to handle a formula that contains 8 "IF" 's... And I know excel is only supposed to be able to handle 7 "IF" 's... Is there any way around this? I cannot do without any of the "IF" 's, this is the minimum...

    Tia,

    kipaaa
    The Netherlands

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, and welcome to the board.

    Yes, you can do two cells with nesting, then use a third that references those two. (I have nested as many as 15 IF's, but it becomes unwieldy and difficult to troubleshoot)

    However, a lookup table is far superior, more flexible, and easier to trouble shoot.

    Can you post a sample of the data and what you are looking to do?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jul 2007
    Posts
    6
    I've tried making the formula a bit more readable by putting the IF on a new line... The purpose of the formula is to check 8 different conditions on which the result can be "voldoet".

    Ofcourse I could split the formula over two cells and make a third cell with a formula that checks the previous two... But i really like throwing everything in once cell :-)

    Formula:
    =IF(AND(L3="Pre brede herwaardering";M3="Box 3";N3="Vrijstelling";P3<=4748;E3<11050;E3>7672;K3< =15);"Voldoet";
    IF(AND(L3="Pre brede herwaardering";M3="Box 3";N3="Vrijstelling";P3<=4748;E3>11050;K3<=20);"Vo ldoet";
    IF(AND(L3="Pre brede herwaardering";M3="Box 3";N3="Vrijstelling";P3<=4748;E3<=7640;E3>=5845;K3 <=10);"Voldoet";
    IF(AND(L3="Pre brede herwaardering";M3="Box 3";N3="Vrijstelling";P3<=4748;E3>=4748;E3<=5844;K3 <=5);"Voldoet";
    IF(AND(L3="Brede herwaardering";M3="Box 3";N3="Vrijstelling";P3>=7640;K3<=10);"Hoge vrijstelling";
    IF(AND(L3="Brede herwaardering";M3="Box 3";N3="Vrijstelling";P3>=5844;P3<7640;K3<=10);"Lag e vrijstelling";
    IF(AND(L3="Kapitaalverzekering Eigen Woning";M3="Box 1";P3>=7640;K3<=10);"Hoge vrijstelling";
    IF(AND(L3="Kapitaalverzekering Eigen Woning";M3="Box 1";P3>=5844;P3<7640;K3<=10);"Lage vrijstelling";"Controleer!")))))))

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, but can you set up a table that has these conditions? Even a 30 row table (for all possible combinations) with a VLOOKUP formula will be faster and more efficient than what you have.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Jul 2007
    Posts
    6
    I split the conditions over a couple of cells, now it works.

    But you're suggestion sounds more efficient. Question is: how do is set up a table with these conditions. I have to check 5000+ rows for these conditions...

    Learning more every day :-)

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    You would have a table with all possible combinations in Columns B-F, then use concatenation in column A (all the outcomes associated with each combination). If you name the table, then you can use it in the VLOOKUP formula. It will be a little larger than most, but will still work. Just test the values in Columns containing P3 and E3, since the lookup guides how to setup the numbers.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Jul 2007
    Posts
    6
    Thanks again for your help!
    The table isn't quite ready yet, but so far using a table instead of a lot of IF functions has reduced the size of the sheet from 38 MB to 18 MB!

Posting Permissions

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