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

Week1	Week2	%
I	P-W	20%
I	P-H	20%
P-W	I	-20%
P-W	P-H	20%
P-W	K	20%
P-H	K	0%
K	K-C	10%
K	CC	20%
K	K-R	-20%
I	I	0%
P-W	P-W	0%
P-H	P-H	0%
K	K	0%
K-C	K-C	0%
CC	CC	0%

<colgroup><col width="63" span="3" style="width:47pt"> </colgroup><tbody>
</tbody>

 

[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" )))