Need Parentheses Help

[mrpearl11]

I need parentheses help. I keep getting the error that I have one or more extra closed parentheses.

Here is my formula:
=IF(AZ5*.8)+AO5<50, (AZ5*.8)+AO5*0.11, IF(AZ5*.8)+AO5<1000, 5.50+((AZ5*.8)+AO5-50)*0.06, 6+57+((AZ5*.8)+AO5 -1000)*0.02)

What I am trying to do is:
Give 20% off AZ.
Add AO to AZ with 20% off.
if AZ+AO is less than 50 then multiply the sum of AZ*.8 plus AO by .11
if AZ+AO is less than 1000 then multiply the sum of AZ*.8 plus AO by .06
if AZ+AO is more than than 1000 then multiply the sum of AZ*.8 plus AO by .02

Any help would be appreciated.

 

[mikerickson]

Just off the top of my head, try adding a ( after the 3rd character

=IF((AZ5*...

I just also saw that the nested IF is also missing a (

 

[mrpearl11]

=IF((AZ5*.8)+AO5<50, (AZ5*.8)+AO5*0.11, IF(AZ5*.8)+AO5<1000, 5.50+((AZ5*.8)+AO5-50)*0.06, 6+57+((AZ5*.8)+AO5 -1000)*0.02)

Like this?

 

[mikerickson]

^^ Does it work?

 

[mrpearl11]

I made a few changes

=IF((AZ5*.8)+AO5<50, ((AZ5*.8)+AO5)*0.11, IF((AZ5*.8)+AO5<1000, 5.50+((AZ5*.8)+AO5-50)*0.06, 5.50+60+((AZ5*.8)+AO5 -1000)*0.02))

The first two sections work with your input while the last section gives me a number too high.

If you make AZ to be 1200
AO to be 17.04

I would have expected the answer to be $53.54 instead the answer was $61.12.

 

[mrpearl11]

does the logic i used to create the formula make sense?

 

[shg]

Maybe ...

=(80%*AZ5 + AO5) * LOOKUP(80%*AZ5 + AO5, {0,50,1000}, {11,6,2}%)

 

[mrpearl11]

that's getting close. trying to get it to be $53.54 when the value is 1200.

 

[shg]

that's getting close. trying to get it to be $53.54 when the value is 1200.

if AZ+AO is more than than 1000 then multiply the sum of AZ*.8 plus AO by .02

1200 * 2% is 24, not 53.54

 

[Jonmo1]

It's probably to do with the order Excel calculates.
It does * and / before + and -
And anything inside parens () has priority

so this
5.50+60+((AZ5*.8)+AO5 -1000)*0.02
may not be doing EXACTLY what you think it is..

it's doing it in this order
Step1. AZ5*.8
Step2. (Step1)+AO5 -1000
Step3. (Step2)*0.02
Step4. 5.50+60+Step3

Perhaps you want this
(5.50+60+((AZ5*.8)+AO5 -1000))*0.02

Step1. AZ5*.8
Step2. (Step1)+AO5 -1000
Step3. 5.50+60+(Step2)
Step4. (Step3)*0.02