If Formula Error

[Mangesharalkar]

Hi All,

I am entering the below formula but it says missing parenthesis

=IF(AND(C10>0%,C10<80%),0,IF(AND(C10>=80%,C10<=100%),C7*C10,IF(AND(C10>=100%,C10<=120%),(C5*0.5%+(C7-C5)*1.5%,IF(C10>120%,(C5*0.75%+C5*120%*1.5%+(C7-C5*120%)*2%

Can any one help

C5 is Domain Target
C7 is Achievement


Range is below

Range	Incentive %
0-80%	Nil
80-100%	0.75%
100 -120 %	1.50%
120 & above%	2.00%

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[EvilC]

Hi All,

I am entering the below formula but it says missing parenthesis

=IF(AND(C10>0%,C10<80%),0,IF(AND(C10>=80%,C10<=100%),C7*C10,IF(AND(C10>=100%,C10<=120%),(C5*0.5%+(C7-C5)*1.5%,IF(C10>120%,(C5*0.75%+C5*120%*1.5%+(C7-C5*120%)*2%

Can any one help

C5 is Domain Target
C7 is Achievement


Range is below

Range	Incentive %
0-80%	Nil
80-100%	0.75%
100 -120 %	1.50%
120 & above%	2.00%

<tbody>
</tbody>

Hey,

You need to close your IFS:

=IF(AND(C10>0%,C10<80%),0,IF(AND(C10>=80%,C10<=100%),C7*C10,IF(AND(C10>=100%,C10<=120%),(C5*0.5%+(C7-C5)*1.5%,IF(C10>120%,(C5*0.75%+C5*120%*1.5%+(C7-C5*120%)*2%))))))

 

[BarryL]

Brackets also need to be closed inside the formula not just on the end.

=IF(AND(C10>0%,C10<80%),0,IF(AND(C10>=80%,C10<=100%),C7*C10,IF(AND(C10>=100%,C10<=120%),(C5*0.5%+(C7-C5)*1.5%),IF(C10>120%,(C5*0.75%+C5*120%*1.5%+(C7-C5*120%)*2%),FALSE))))

 

[EvilC]

BarryL you are correct. Escaped my old tired brain.

 

[Yongle]

You may find it easier to spot problems by splitting the formula onto separate lines in the cell with {Alt}{Enter}
- Excel's colour coding help match opening and closing parentheses on each line
- count opening IF parentheses and match with closing ones

=IF(AND(C10>0%,C10<80%),0,
IF(AND(C10>=80%,C10<=100%),C7*C10,
IF(AND(C10>=100%,C10<=120%),(C5*0.5%+(C7-C5)*1.5%),
IF(C10>120%,(C5*0.75%+C5*120%*1.5%+(C7-C5)*120%*2%),"something missing here"))))