Odd formula error

[Ian Mellors]

Why does this work;

=IF(F7=0,"",IF(J7=0,"",IF(N7=0,"",IF(R7=0,"",IF(V7=0,"",IF
(AND((COUNTA(F7,J7,N7,R7,V7,Z7)+E7)>13,COUNTA(Z7)
=1),"X",""))))))

but this throws an error

=IF(F7=0,"",IF(J7=0,"",IF(N7=0,"",IF(R7=0,"",IF(V7=0,"",IF
(Z7=0,"",IF(AND((COUNTA(F7,J7,N7,R7,V7,Z7,AD7)+E7)

13,COUNTA(AD7)=1),"X","")))))))

too long?, I havn't exceded the limit if 7 nested IFs ....

 

[Frank Kabel]

Hi
You have exceede the maximum of 7 nested functions (this restriction
counts all nested functions not only the IF function)

Not fully tested but you may try the following
=IF(SUMPRODUCT(--(F7:V7=0),--(MOD(COLUMN(F7:V7),4)=2))>0,"",IF(AND(COUN
TA(F7,J7,N7,R7,V7,Z7,AD7)+E7>13,AD7<>""),"X",""))

 

[JE McGimpsey]

The limit applies to nested functions, not just IF()s.

One way (assuming your cells are numeric):

=IF(F7*J7*N7*R7*V7*Z7=0,"",IF(AND(...

 

[Frank Kabel]

just a small addition. Make this
=IF(SUMPRODUCT(--(F7:Z7=0),--(MOD(COLUMN(F7:Z7),4)=2))>0,"",IF(AND(COUN
TA(F7,J7,N7,R7,V7,Z7,AD7)+E7>13,AD7<>""),"X",""))


to include the Z column as well

 

[Ian Mellors]

Thanks guys,

I arrived at the following alternative (that is more
elegant nyway IMHO)

=IF(AND((COUNTA(F7,J7,N7,R7,V7,Z7)+E7>=13),COUNTA(AD7)
=1),"X","")