Excel Percentage Formula

kideal · #1 06-04-2018, 05:01 AM

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 · #2 06-04-2018, 08:09 AM

With condition in A2:

Code:

=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 · #3 06-05-2018, 02:54 AM

I placed the following values in cells

Code:

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

Then with the Salary level in cell A2 the formula is

Code:

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

Shashi Kant · #4 06-07-2018, 03:38 AM

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 · #5 06-07-2018, 06:00 AM

Thanks for the formula it works great.

kideal · #6 06-07-2018, 06:19 AM

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 · #7 06-07-2018, 10:54 PM

Dear Kideal,

Kindly use below formula...

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

kideal · #8 06-08-2018, 05:19 AM

Thank you works fine.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
percentage calculation formula	yoyo4	Excel	10	11-15-2017 11:44 AM
Percentage of . . .	chaserwill	Excel	1	03-10-2017 09:28 AM
What is the formula for getting the percentage for time	phoenix666!	Excel	1	07-29-2016 08:18 AM
overall percentage	ketanco	Project	1	08-04-2012 05:56 AM
percentage on columns	911	Excel	1	09-01-2010 09:28 AM