too many levels of nesting

**gss** · 03-31-2009, 10:08 PM

I have the following formulas which when I try to put in excel, it says I have too many levels of nesting:

=IF(K4="","",IF(AND(Hta_1>=D4,Hba_1<=D4,Hta_1<>"",Hba_1<>""),1,IF(AND(Hta_2>D4,Hba_2<=D4,Hta_2<>"",Hba_2<>""),2,IF(AND(Hta_3>D4,Hba_3<=D4,Hta_3<>"",Hba_3<>""),3,IF(AND(Hta_4>D4,Hba_4<=D4,Hta_4<>"",Hba_4<>""),4,IF(AND(Hta_5>D4,Hba_5<=D4,Hta_5<>"",Hba_5<>""),5 ,IF(AND(Hta_6>D4,Hba_6<=D4,Hta_6<>"",Hba_6<>""),6,IF(AND(Hta_7>D4,Hba_7<=D4,Hta_7<>"",Hba_7<>""),7,"No reinforcements"))))))

=IF(L4="","",IF(AND(L4=1,MAX(Tcha_1,Tpa_1,Dsra_1)<>0),MAX(Tcha_1,Tpa_1,Dsra_1),IF(AND(L4=2,MAX(Tcha_2,Tpa_2,Dsra_2)<>0),MAX(Tcha_2,Tpa_2,Dsra_2),IF(AND(L4=3,MAX(Tcha_3,Tpa_3,Dsra_3)<>0),MAX(Tcha_3,Tpa_3,Dsra_3),IF(AND(L4=4,MAX(Tcha_4,Tpa_4,Dsra_4)<>0),MAX(Tcha_4,Tpa_4,Dsra_4),IF(AND(L4=5,MAX(Tcha_4,Tpa_4,Dsra_4)<>0),MAX(Tcha_5,Tpa_5,Dsra_5), IF(AND(L4=6,MAX(Tcha_6,Tpa_6,Dsra_6)<>0),MAX(Tcha_6,Tpa_6,Dsra_6), IF(AND(L4=7,MAX(Tcha_7,Tpa_7,Dsra_7)<>0),MAX(Tcha_7,Tpa_7,Dsra_7),"-"))))))

Can anyone help with this and also explain how to get rid of the error?

Thanks.

**GuruWannaB** · 03-31-2009, 11:22 PM

Excel 2003 has a limit of how many IF statements you can "nest" inside one another, 7 being the maximum. You are getting the error because you have more than 7 IF statements in your formulas. Try breaking it up between 2 helper cells and then have your result formula in a 3rd cell

Example...break up your formula

Please Login or Register  to view this content.

into the following three cells

IE. Helper Cell 1

Please Login or Register  to view this content.

Helper Cell 2

Please Login or Register  to view this content.

Helper Cell 3 (Change the HELPERCELL1 and helpercell2 with the actual cell locations)

Please Login or Register  to view this content.

**gss** · 04-01-2009, 12:57 PM

This is already an existing spreadsheet that I'm modifying, so I really don't have any room to add any more helper cells. I had gotten help on this issue a little while earlier, but I don't remember nor understand how to break them up. I do know that the 'if' statements were separated by '+'. Does anyone know how I can do this.

Also, I am using 2007.

**arthurbr** · 04-01-2009, 01:02 PM

I'm not sure about 2007 but there's a workaround at this site

**NBVC** · 04-01-2009, 01:23 PM

You could try:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

**gss** · 04-02-2009, 03:10 PM

Looking at the first code:

The code is working when I leave it as is, but instead of returning a value of '0' if all of the statements are false, I want it to return the phrase "No reinforcements". However, when I change that last '0' in the code to "No reinforcements", I get an error that says #VALUE!.

Can you tell me what I'm doing wrong or how to fix it?

Thanks.

**NBVC** · 04-02-2009, 03:43 PM

...a bit ugly but...

Please Login or Register  to view this content.

you can also keep the original formula and custom format cell to something like: General;General;"No Reinforcements"

so that if the result is 0, the text string appears instead.