Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-04-2018, 05:01 AM
kideal kideal is offline Excel Percentage Formula Windows 10 Excel Percentage Formula Office 2016
Novice
Excel Percentage Formula
 
Join Date: Jun 2018
Posts: 4
kideal is on a distinguished road
Default Excel Percentage Formula


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

Last edited by kideal; 06-04-2018 at 07:57 AM.
Reply With Quote
  #2  
Old 06-04-2018, 08:09 AM
ArviLaanemets ArviLaanemets is offline Excel Percentage Formula Windows 8 Excel Percentage Formula Office 2016
Expert
 
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

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
Reply With Quote
  #3  
Old 06-05-2018, 02:54 AM
Roger Govier Roger Govier is offline Excel Percentage Formula Windows 10 Excel Percentage Formula Office 2016
Novice
 
Join Date: Oct 2017
Location: Abergavenny, Wales, UK
Posts: 13
Roger Govier is on a distinguished road
Default

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)
Reply With Quote
  #4  
Old 06-07-2018, 03:38 AM
Shashi Kant Shashi Kant is offline Excel Percentage Formula Windows 7 32bit Excel Percentage Formula Office 2016
Novice
 
Join Date: May 2018
Posts: 16
Shashi Kant is on a distinguished road
Default

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%)))
Attached Files
File Type: xlsx Book2.xlsx (8.7 KB, 9 views)
Reply With Quote
  #5  
Old 06-07-2018, 06:00 AM
kideal kideal is offline Excel Percentage Formula Windows 10 Excel Percentage Formula Office 2016
Novice
Excel Percentage Formula
 
Join Date: Jun 2018
Posts: 4
kideal is on a distinguished road
Default

Thanks for the formula it works great.
Reply With Quote
  #6  
Old 06-07-2018, 06:19 AM
kideal kideal is offline Excel Percentage Formula Windows 10 Excel Percentage Formula Office 2016
Novice
Excel Percentage Formula
 
Join Date: Jun 2018
Posts: 4
kideal is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 06-07-2018, 10:54 PM
Shashi Kant Shashi Kant is offline Excel Percentage Formula Windows 7 32bit Excel Percentage Formula Office 2016
Novice
 
Join Date: May 2018
Posts: 16
Shashi Kant is on a distinguished road
Default

Dear Kideal,


Kindly use below formula...

=IF(A2<=10000,A2*15%,IF(A2<=83333,1500+(A2-10000)*20%,16166.6+(A2-83333)*30%))
Attached Files
File Type: xlsx Book2.xlsx (8.6 KB, 10 views)
Reply With Quote
  #8  
Old 06-08-2018, 05:19 AM
kideal kideal is offline Excel Percentage Formula Windows 10 Excel Percentage Formula Office 2016
Novice
Excel Percentage Formula
 
Join Date: Jun 2018
Posts: 4
kideal is on a distinguished road
Default

Thank you works fine.
Reply With Quote
Reply



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
Excel Percentage Formula overall percentage ketanco Project 1 08-04-2012 05:56 AM
percentage on columns 911 Excel 1 09-01-2010 09:28 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:36 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft