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
1	Amt		Low Limit	High Limit	Percent	Differential
2	24,300.00		-	8,555.50	0%	0%
3			8,555.50	10,000.00	15%	15%
4	Result		10,000.00	83,333.00	20%	5%
5	3076.675		83,333.00	99,999,999.00	30%	10%

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula A5 =SUMPRODUCT((A2-C2:C5),--(A2>C2:C5),F2:F5) <tbody> </tbody>

<tbody>
</tbody>

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.