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
[TABLE="width: 173"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Range[/TD]
[TD]Incentive %[/TD]
[/TR]
[TR]
[TD]0-80%[/TD]
[TD]Nil[/TD]
[/TR]
[TR]
[TD]80-100%[/TD]
[TD="align: right"]0.75%[/TD]
[/TR]
[TR]
[TD]100 -120 %[/TD]
[TD="align: right"]1.50%[/TD]
[/TR]
[TR]
[TD]120 & above%[/TD]
[TD="align: right"]2.00%[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
[TABLE="width: 173"]
<tbody>[TR]
[TD]Range[/TD]
[TD]Incentive %[/TD]
[/TR]
[TR]
[TD]0-80%[/TD]
[TD]Nil[/TD]
[/TR]
[TR]
[TD]80-100%[/TD]
[TD="align: right"]0.75%[/TD]
[/TR]
[TR]
[TD]100 -120 %[/TD]
[TD="align: right"]1.50%[/TD]
[/TR]
[TR]
[TD]120 & above%[/TD]
[TD="align: right"]2.00%[/TD]
[/TR]
</tbody>[/TABLE]

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