Excel Percentage Formula

[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

[ArviLaanemets]

With condition in A2:

=CHOOSE(MATCH($A$2,{0,8555.5,10000,83333,99999999},1),0,0.15,0.2,0,3)

Format the cell with formula as percent

[Roger Govier]

I placed the following values in cells

D2 8555.5
E2 15%
D3 10000
E3 20%
D4 83333
E4 30%

Then with the Salary level in cell A2 the formula is

=SUM((A2>D4)*(A2-D4)*E4,(A2>D3)*(MAX(D4,A2-D3))*E3,(A2>D2)*(D3-D2)*E2)

[Shashi Kant]

Dear Friend,


Kindly find the attachment, it might be helpful for you.
Your Value in Range("A2") and the used formula is below.

=IF(A2<=8555.5,0,IF(A2<=10000,(A2-8555.5)*15%,IF(A2<=83333,216.675+(A2-10000)*20%,14883.28+(A2-83333)*30%)))

[kideal]

Thanks for the formula it works great.

[kideal]

For the first example the formula works fine, thank you.

Can you amend your formula for this one below:

Example if you have a gross of 24300

Bracket 1 up-to 10,000 10,000.00 15% 1,500.00
Bracket 2 10,000.01 - 83,333 14,300.00 20% 2,860.00
Bracket 3 >83,333 0.00 30% 0.00
24,300.00 4,360.00

[Shashi Kant]

Dear Kideal,


Kindly use below formula...

=IF(A2<=10000,A2*15%,IF(A2<=83333,1500+(A2-10000)*20%,16166.6+(A2-83333)*30%))

[kideal]

Thank you works fine.