If Formula Error

[Mangesharalkar]

Hi All,

Below is the data:

I am trying formula, but giving error:

=IF(AND(O5="I",P5="P-W",20%,IF(AND(O5="I",P5="P-H"),20%,IF(AND(O5="I",P5="I"),0%,IF(AND(O5="P-W",P5="I"),-20%,IF(AND(O5="P-W",P5="P-H",20%,IF(AND(O5=P-W,P5="K",20%,IF(AND(O5="P-W",P5="P-W",0%,IF(AND(O5="K",P5="CC",20%,IF(AND(O5="K",P5="K-R",-20%,IF(AND(O5="K-C",P5="K-C",0%,IF(AND(O5="P-H",P5="P-H",0%,IF(AND(O5="K",P5="C-C",20%,IF(AND(O5="P-H",P5="K",0%,IF(AND(O5="K",P5="K",0%,IF(AND(O5="CC",P5="CC",0%)

Error is - Formula Opening and closing is missing Parenthesizing

[TABLE="width: 189"]
<colgroup><col width="63" span="3" style="width:47pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 63"]Week1[/TD]
[TD="class: xl67, width: 63"]Week2[/TD]
[TD="class: xl67, width: 63"]%[/TD]
[/TR]
[TR]
[TD="class: xl69"]I[/TD]
[TD="class: xl69"]P-W[/TD]
[TD="class: xl68, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl69"]I[/TD]
[TD="class: xl69"]P-H[/TD]
[TD="class: xl68, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl69"]P-W[/TD]
[TD="class: xl69"]I[/TD]
[TD="class: xl68, align: right"]-20%[/TD]
[/TR]
[TR]
[TD="class: xl69"]P-W[/TD]
[TD="class: xl69"]P-H[/TD]
[TD="class: xl68, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl69"]P-W[/TD]
[TD="class: xl69"]K[/TD]
[TD="class: xl68, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl69"]P-H[/TD]
[TD="class: xl69"]K[/TD]
[TD="class: xl68, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl69"]K[/TD]
[TD="class: xl69"]K-C[/TD]
[TD="class: xl68, align: right"]10%[/TD]
[/TR]
[TR]
[TD="class: xl69"]K[/TD]
[TD="class: xl69"]CC[/TD]
[TD="class: xl68, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl69"]K[/TD]
[TD="class: xl69"]K-R[/TD]
[TD="class: xl68, align: right"]-20%[/TD]
[/TR]
[TR]
[TD="class: xl69"]I[/TD]
[TD="class: xl69"]I[/TD]
[TD="class: xl68, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl69"]P-W[/TD]
[TD="class: xl69"]P-W[/TD]
[TD="class: xl68, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl69"]P-H[/TD]
[TD="class: xl69"]P-H[/TD]
[TD="class: xl68, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl70"]K[/TD]
[TD="class: xl70"]K[/TD]
[TD="class: xl68, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl69"]K-C[/TD]
[TD="class: xl69"]K-C[/TD]
[TD="class: xl68, align: right"]0%[/TD]
[/TR]
[TR]
[TD="class: xl71"]CC[/TD]
[TD="class: xl71"]CC[/TD]
[TD="class: xl68, align: right"]0%
[/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

your missing brackets after the AND|()
AND(O5="I",P5="P-W"

=IF(AND(O5="I",P5="P-W",20%,IF(AND(O5="I",P5="P-H"),20%,IF(AND(O5="I",P5="I"),0%,IF(AND(O5="P-W",P5="I"),-20%,IF(AND(O5="P-W",P5="P-H",20%,IF(AND(O5=P-W,P5="K",20%,IF(AND(O5="P-W",P5="P-W",0%,IF(AND(O5="K",P5="CC",20%,IF(AND(O5="K",P5="K-R",-20%,IF(AND(O5="K-C",P5="K-C",0%,IF(AND(O5="P-H",P5="P-H",0%,IF(AND(O5="K",P5="C-C",20%,IF(AND(O5="P-H",P5="K",0%,IF(AND(O5="K",P5="K",0%,IF(AND(O5="CC",P5="CC",0%)

and a FALSE condition at the end and loads of brackets to close each IF

=IF(AND(O5="I",P5="P-W",20%,IF(AND(O5="I",P5="P-H"),20%,IF(AND(O5="I",P5="I"),0%,IF(AND(O5="P-W",P5="I"),-20%,IF(AND(O5="P-W",P5="P-H",20%,IF(AND(O5=P-W,P5="K",20%,IF(AND(O5="P-W",P5="P-W",0%,IF(AND(O5="K",P5="CC",20%,IF(AND(O5="K",P5="K-R",-20%,IF(AND(O5="K-C",P5="K-C",0%,IF(AND(O5="P-H",P5="P-H",0%,IF(AND(O5="K",P5="C-C",20%,IF(AND(O5="P-H",P5="K",0%,IF(AND(O5="K",P5="K",0%,IF(AND(O5="CC",P5="CC",0%, "????"))))))))))))))))

for the same TRUE ie 20%
you could put in all one statement mixing OR () with AND()

for example
IF(AND(O5="I",P5="P-W" . =true = 20%
AND(O5="P-W",P5="P-H" . if true = 20%

so combine to
IF (OR(AND(O5="I",P5="P-W"),AND(O5="P-W",P5="P-H")), 20% ,
and the other 20% can also be added to the OR

 

[Michael M]

try...

=IF(AND(O12="I",P12="P-W"),20%,IF(AND(O12="I",P12="P-H"),20%,IF(AND(O12="I",P12="I"),0%,IF(AND(O12="P-W",P12="I"),-20%,IF(AND(O12="P-W",P12="P-H"),20%,IF(AND(O12="P-W",P12="K"),20%,IF(AND(O12="P-W",P12="P-W"),0%,IF(AND(O12="K",P12="CC"),20%,IF(AND(O12="K",P12="K-R"),-20%,IF(AND(O12="K-C",P12="K-C"),0%,IF(AND(O12="P-H",P12="P-H"),0%,IF(AND(O12="K",P12="C-C"),20%,IF(AND(O12="P-H",P12="K"),0%,IF(AND(O12="K",P12="K"),0%,IF(AND(O12="CC",P12="CC"),0%)))))))))))))))

 

[footoo]

If you have Excel 2016, you might want to look at the IFS function.

 

[Michael M]

bit shorter...
A VLOOKUP with ALL options listed might have been a better idea !!

=IF(AND(O5="I",OR(P5="P-W",P5="P-H")),20%,IF(AND(O5="I",P5="I"),0%,IF(AND(O5="P-W",OR(P5="I",P5="K")),-20%,IF(AND(O5="P-W",P5="P-H"),20%,IF(AND(O5="P-W",P5="P-W"),0%,IF(AND(O5="K",OR(P5="CC",P5="C-C")),20%,IF(AND(O5="K",P5="K-R"),-20%,IF(AND(O5="K-C",P5="K-C"),0%,IF(AND(O5="P-H",P5="P-H"),0%,IF(AND(O5="P-H",P5="K"),0%,IF(AND(O5="K",P5="K"),0%,IF(AND(O5="CC",P5="CC"),0%))))))))))))

 

[etaf]

i just worked out ALL the OR() and AND()
reduced to 3 nested IFs

=IF(OR(AND(O5="I",P5="P-W"),AND(O5="I",P5="P-H"),AND(O5="P-W",P5="P-H"),AND(O5="P-W",P5="K"),AND(O5="K",P5="CC"),AND(O5="K",P5="C-C")),20%,IF(OR(AND(O5="I",P5="I"),AND(O5="P-W",P5="P-W"),AND(O5="K-C",P5="K-C"),AND(O5="P-H",P5="P-H"),AND(O5="P-H",P5="K"),AND(O5="K",P5="K"),AND(O5="CC",P5="CC")), 0%,
IF(OR( AND(O5="P-W",P5="I"),AND(O5="K",P5="K-R")),-20%,"not met" )))