Nested IF syntax help needed 1

[knifey]

Hi,

Can anyone spot my syntax error in this formula. It's driving me mad. I think it's somewhere in the "?"),"?"),"?"),"?"),"?"),"?")) part.

=IFERROR(IF(AND(P3="NA",ISNUMBER(Z3),Z3>0),Z3,IF(AND(P3<>"NA",ISNUMBER(Z3),Z3>0,ISNUMBER(AA3),AA3>0),IF(AND(M3<=Q3,S3<=O3),O3-M3,,IF(AND(M3<=Q3,O3<=S3),S3-M3,,IF(AND(Q3<=M3,S3<=O3),S3-Q3,,IF(AND(Q3<=M3,S3<=O3),O3-Q3,"?"),"?"),"?"),"?"),"?"),"?"))

Kind regards,
K

 

[SkipVought]

hi,

Start from the inside and work you way out. It takes effort, organization and persistence. It's how debugging is done.

Does this help?
[tt]
=IFERROR
(
IF(AND(P3="NA",ISNUMBER(Z3),Z3>0),Z3,
IF(AND(P3<>"NA",ISNUMBER(Z3),Z3>0,ISNUMBER(AA3),AA3>0),
IF(AND(M3<=Q3,S3<=O3),O3-M3,,
IF(AND(M3<=Q3,O3<=S3),S3-M3,,
IF(AND(Q3<=M3,S3<=O3),S3-Q3,,
IF(AND(Q3<=M3,S3<=O3),O3-Q3,"?"),"?"),"?"),"?"),"?"),"?")
)
[/tt]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

This "works" but who knows if the logic is correct???

[tt]
=IFERROR(
IF(AND(P3="NA",ISNUMBER(Z3),Z3>0),Z3,
IF(AND(P3<>"NA",ISNUMBER(Z3),Z3>0,ISNUMBER(AA3),AA3>0),[highlight]"WHAT",[/highlight]
IF(AND(M3<=Q3,S3<=O3),O3-M3,[highlight] [/highlight]
IF(AND(M3<=Q3,O3<=S3),S3-M3,[highlight] [/highlight]
IF(AND(Q3<=M3,S3<=O3),S3-Q3,[highlight] [/highlight]
IF(AND(Q3<=M3,S3<=O3),O3-Q3,"?")[highlight]))))),"ERR")[/highlight][/tt]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

In case of mutually exclusive conditions you can flatten the formula structure.
Condition:
=1+IF(AND(P3="NA",ISNUMBER(Z3),Z3>0),1,0)+IF(AND(P3<>"NA",ISNUMBER(Z3),Z3>0,ISNUMBER(AA3),AA3>0),2,0)+IF(AND(M3<=Q3,S3<=O3),3,0)+IF(AND(M3<=Q3,O3<=S3),4,0)+IF(AND(Q3<=M3,S3<=O3),5,0)+IF(AND(Q3<=M3,S3<=O3),6,0)
Switch:
=CHOOSE(Condition,No_match, Formula1,....., Formula6)
For independent conditions the binary representation can be used (1, 2, 4, 8 if any IF is TRUE, 1, 2...., 16 options for CHOOSE function). At least this can simplify debugging.

combo

 

[knifey]

Thanks all