Formula error

[Netaji]

Hi all,

I have some problem with IF(AND formula.

I dont know where i am erring.Can anyone help? Where i am missing parenthesis or something else.

=IF(OR(C12<150,D9="NO DATA"),"NO",IF(AND(MIN(H15:H25)>L11,C11>(K11+(J11-K11)*100%),C11<(K11+(J11-K11)*150%),C11<(L11+(L11*1.5%)),"bad",IF(AND(MAX(G15:G25)<L11,C12<(J11-(J11-K11)*100%),C12>(J11-(J11-K11)*150%),C12>(L11-(L11*1.5%)),"good","Neutral"))


Thanks,

Netaji

[Danerida]

You hadn't closed the AND() statements, or each of the IF() statements. i.e. not enough brackets. Try:

=IF(OR(C12<150,D9="NO DATA"),"NO",IF(AND(MIN(H15:H25)>L11,C11>(K11+(J11-K11)*100%),C11<(K11+(J11-K11)*150%),C11<(L11+(L11*1.5%))),"bad",IF(AND(MAX(G15:G25)<L11,C12<(J11-(J11-K11)*100%),C12>(J11-(J11-K11)*150%),C12>(L11-(L11*1.5%))),"good","Neutral")))

[FDibbins]

It is often handy to add indents (or spaces) to a complex formula like this, so you can see where things fit and nest etc...

=IF(OR(C12<150,D9="NO DATA"),"NO",

    IF(AND(MIN(H15:H25)>L11,C11>(K11+(J11-K11)*100%),C11<(K11+(J11-K11)*150%),C11<(L11+(L11*1.5%))),"bad",

           IF(AND(MAX(G15:G25)<L11,C12<(J11-(J11-K11)*100%),C12>(J11-(J11-K11)*150%),C12>(L11-(L11*1.5%))),"good","Neutral")))

THis showed me you did not close the parentheses on both AND's, just before "bad" and just before "good