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

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?

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

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.

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

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.

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!