Nested "IF" Function Help???

[~EVH~]

Hi Folks,

I'm having a problem with a formula that has a series of "Nested IF" functions in it. Everything works fine, UNTIL I add the LAST "IF" function, then Excel 2003 returns, "The formula you typed contains an error" and highlights the second "AND" in the formula.

Here's the formula in its entirety:

=IF(OR(AK3>=1.6,AND(AG3>=1.6,AI3=0)),1,IF(AND(AG3>=1.6,AI3>=1.6),1,IF(AND(AE3=1,AG3>=1.6),1,IF(AND(AG3=0,AI3>=1.6),2,IF(AND(AK3>=1.3,AK3<=1.59),3,IF(AND(AI3=0,AND(AG3>=1.3,AG3<=1.59)),3,IF(AND(AG3=0,AND(AI3>=1.3,AI3<=1.59)),4,"N/A")))))))

Here it is again in a more user-friendly viewing format:

=IF(OR(AK3>=1.6,AND(AG3>=1.6,AI3=0)),1,
IF(AND(AG3>=1.6,AI3>=1.6),1,
IF(AND(AE3=1,AG3>=1.6),1,
IF(AND(AG3=0,AI3>=1.6),2,
IF(AND(AK3>=1.3,AK3<=1.59),3,
IF(AND(AI3=0,AND(AG3>=1.3,AG3<=1.59)),3, <--- works fine to here
IF(AND(AG3=0,AND(AI3>=1.3,AI3<=1.59)),4,"N/A" <--- causes error
)))))))

The outcome is just a table that, in the far left column, RANKS (from 1 to 4) the data in the cells to the right.

Any help would be most appreciated. I'd attach a "TEST" spreadsheet I've been working with to make this easier, but didn't see a way to do that here?

Thanks in advance!

 

[~EVH~]

Bump... any thoughts???

 

[barry houdini]

Looks like you have exceeded the limit for nested functions (7). You could combine some of your ORs and ANDs where the result should be the same, also you can have more than 2 conditions in an AND so you dont need

AND(x,AND(y,z))

you can use

=AND(x,y,z)

so formula can be

=IF(OR(AK3>=1.6,AND(AG3>=1.6,OR(AI3=0,AI3>=1.6,AE3=1))),1,IF(AND(AG3=0,AI3>=1.6),2,IF(OR(AND(AK3>=1.3,AK3<=1.59),AND(AI3=0,AG3>=1.3,AG3<=1.59)),3,IF(AND(AG3=0,AI3>=1.3,AI3<=1.59),4,"N/A"))))

 

[~EVH~]

Looks like you have exceeded the limit for nested functions (7). You could combine some of your ORs and ANDs where the result should be the same, also you can have more than 2 conditions in an AND so you dont need

AND(x,AND(y,z))

you can use

=AND(x,y,z)

so formula can be

=IF(OR(AK3>=1.6,AND(AG3>=1.6,OR(AI3=0,AI3>=1.6,AE3=1))),1,IF(AND(AG3=0,AI3>=1.6),2,IF(OR(AND(AK3>=1.3,AK3<=1.59),AND(AI3=0,AG3>=1.3,AG3<=1.59)),3,IF(AND(AG3=0,AI3>=1.3,AI3<=1.59),4,"N/A"))))

Thank you, Barry... much appreciated!!!