Percentages by band

[kideal]

I wish do a formula to calculate the percentage with these conditions stated below:

0 - 8,555.5 = 0%
8,555.51 - 10,000 = 15%
10,000 - 83,333 = 20%
>83,333 = 30%

 

[steve the fish]

Try like this:

=LOOKUP(A1,{0,8555.5,10000,83333}+0.01,{0,0.15,0.2,0.3})

 

[kideal]

Thank for the quick reply but you misunderstood, find example below:

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
Bracket 2	8,555.51 - 10,000	1,444.50	15%	216.68
Bracket 3	10,000 - 83,333	14,300.00	20%	2,860.00
Bracket 4	>83,333	0.00	30%	0.00
		24,300.00		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
Bracket 2	8,555.51 - 10,000	1,444.50	15%	216.68
Bracket 3	10,000 - 83,333	40,000.00	20%	8,000.00
Bracket 4	>83,333	0.00	30%	0.00
		50,000.00		8,216.68

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[kideal]

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

 

[sheetspread]

Look here:

https://www.mrexcel.com/forum/excel-questions/912202-calculating-commission.html

Adding a fourth tier isn't difficult, we'll help if you get stuck

 

[Eric W]

First, please do not open multiple threads for the same question.

Second, look at the answer here:

https://www.mrexcel.com/forum/excel-questions/1058079-calculating-percentage-amount-gross.html