IF function with too many arguments

[ahutyra]

Thanks in advance for the assistance.

I am attempting to create a formula that will calculate a percentage of an
amount based on three different percentages. (Huh?)
1. If the amount is 300000 or less, it gets 15% (or 45000).
2. If the amount is 300001 to 500000, the first 300000 is at 15% and the
remainder is calculated at 13% (so 499999 = 70999.87).
My formula through this point is:
=IF(C6=0.15,B6*0.15,IF(C6=0.13,(B6-300000)*0.13+45000,0))
3. My problem comes in when I am adding the third calculation which is if
the amount is 500001 or over, then the first 300000 is 15%, the next 200000
is at 13% and the remainder will be calculated at 11%.
This is the formula I want to use:
=IF(C7=0.15,B7*0.15,IF(C7=0.13,(B7-300000)*0.13+45000,IF(C7=0.11,((B7-300000)*0.13+45000)+(B7-200000)*0.11+65000),0))

Excel is telling me I have too many arguments.
Can anyone help?

I have set up the data in three columns. The first colum is the amount. The
second column is the percentage which I am calculating using this formula:
IF(AND(B2>=1,B2<=300000),15%,IF(AND(B2>=300001,B2<=500000),13%,IF(B2>=500001,11%,0)))
The third column is where I would like the total or percentage amount to end
up.

EXAMPLE OF TABLE
Amount % Total
1000 0.15 150
200000 0.15 30000
300001 0.13 45000.13
333333 0.13 49333.29
499999 0.13 70999.87
500000 0.13 71000

Hope I gave you enough to work with. any help would be great!!!!

 

[Pete_UK]

Have a look at this site:

http://www.mcgimpsey.com/excel/variablerate.html

to see how to do it using SUMPRODUCT.

Hope this helps.

Pete

 

[John C]

You could use the following formula:
=MIN(Amount*15%,300000*15%)+MAX(MIN((Amount-300000)*13%,200000*13%),0)+MAX(MIN((Amount-500000)*11%),0)

Hope this helps.

 

[ahutyra]

Thank you both for your input. Both options work great!!!