Need Parentheses Help

mrpearl11

New Member
Joined
May 24, 2011
Messages
15
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,694
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

New Member
Joined
May 24, 2011
Messages
15
=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?
 

mrpearl11

New Member
Joined
May 24, 2011
Messages
15
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.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,678
Maybe ...

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Forum statistics

Threads
1,078,352
Messages
5,339,712
Members
399,320
Latest member
sut3k

Some videos you may like

This Week's Hot Topics

Top