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:

[TABLE="width: 456"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Bracket[/TD]
[TD] Group [/TD]
[TD]Percentage[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bracket 1[/TD]
[TD] 0 - 8,555.50 [/TD]
[TD]0%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bracket 2[/TD]
[TD] 8,555.51 - 10,000 [/TD]
[TD]15%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bracket 3[/TD]
[TD] 10,000 - 83,333 [/TD]
[TD]20%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bracket 4[/TD]
[TD] >83,333 [/TD]
[TD]30%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Example if you have a gross of 24,300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bracket 1[/TD]
[TD] up-to 8,555.50 [/TD]
[TD]8,555.50[/TD]
[TD]0%[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Bracket 2[/TD]
[TD] 8,555.51 - 10,000 [/TD]
[TD]1,444.50[/TD]
[TD]15%[/TD]
[TD="align: right"]216.68[/TD]
[/TR]
[TR]
[TD]Bracket 3[/TD]
[TD] 10,000 - 83,333 [/TD]
[TD]14,300.00[/TD]
[TD]20%[/TD]
[TD="align: right"]2,860.00[/TD]
[/TR]
[TR]
[TD]Bracket 4[/TD]
[TD] >83,333 [/TD]
[TD]0.00[/TD]
[TD]30%[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24,300.00[/TD]
[TD][/TD]
[TD="align: right"]3,076.68[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Example if you have a gross of 50,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bracket 1[/TD]
[TD] up-to 8,555.50 [/TD]
[TD]8,555.50[/TD]
[TD]0%[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Bracket 2[/TD]
[TD] 8,555.51 - 10,000 [/TD]
[TD]1,444.50[/TD]
[TD]15%[/TD]
[TD="align: right"]216.68[/TD]
[/TR]
[TR]
[TD]Bracket 3[/TD]
[TD] 10,000 - 83,333 [/TD]
[TD]40,000.00[/TD]
[TD]20%[/TD]
[TD="align: right"]8,000.00[/TD]
[/TR]
[TR]
[TD]Bracket 4[/TD]
[TD] >83,333 [/TD]
[TD]0.00[/TD]
[TD]30%[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50,000.00[/TD]
[TD][/TD]
[TD="align: right"]8,216.68[/TD]
[/TR]
</tbody>[/TABLE]

 

[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