Calculating percentage amount of a gross

[kideal]

I am trying to write an excel formula to find the percentage amount from a gross amount where they's a band of different percentages.
Find examples below of what I'm looking for:


Bracket Group Percentage Bracket 1 0 - 8,555.50 0%
Bracket 2 8,555.51 - 10,000 15%
Bracket 3 10,000 - 83,333 20%
Bracket 4 >83,333 30%


Example if you have a gross of 24,300


Bracket 1 up-to 8,555.50 8,555.50 0% 0.00 Amount = 0
Bracket 2 8,555.51 - 10,000 1,444.50 15% Amount = 216.68
Bracket 3 10,000 - 83,333 14,300.00 20% Amount = 2,860.00
Bracket 4 >83,333 0.00 30% 0.00
Total = 3,076.68


Example if you have a gross of 50,000


Bracket 1 up-to 8,555.50 8,555.50 0% 0.00 Amount = 0
Bracket 2 8,555.51 - 10,000 1,444.50 15% Amount = 216.68
Bracket 3 10,000 - 83,333 40,000.00 20% Amount = 8,000.00
Bracket 4 >83,333 0.00 30% Amount = 0.00
Total = 8,216.68

 

[Eric W]

Try:

	A	B	C	D	E	F
Amt	Low Limit	High Limit	Percent	Differential
Result

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"] 24,300.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] 8,555.50[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 8,555.50[/TD]
[TD="align: right"] 10,000.00[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]15%[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"] 10,000.00[/TD]
[TD="align: right"] 83,333.00[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]5%[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3076.675[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 83,333.00[/TD]
[TD="align: right"] 99,999,999.00[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]10%[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A5[/TH]
[TD="align: left"]=SUMPRODUCT((A2-C2:C5),--(A2>C2:C5),F2:F5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Note that you could put the whole table in the formula if you like, but it's much easier to change and understand this way. Also, columns D and E are never used in the formula, they're just there for clarity. Hope this helps.