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!!!!

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

On Aug 8, 12:17*am, ahutyra wrote:
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-30000*0)*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=50000*1,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!!!!

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.
--
John C


"ahutyra" wrote:

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!!!!

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

"John C" wrote:

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.
--
John C


"ahutyra" wrote:

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!!!!