Hello,
I'm trying to get the right formula to calculate commissions based on the following:
Total Commission: x
First $10,000.00 10.0% $
Next $15,000.00 7.5% $
Next $25,000.00 5% $
Next $50,000.00 3% $
Balance $100,000.00 2% $
Depending on what x is, the first 10,000 is paid at 10.0%, then the next 15,000 is paid at 7.5% and so on
Any help would be greatly appreciated!
so to be clear, if the person sells 15,000 they get 1375 in commissions? 1000 for the first 10% on 10,000 then 375 for the 7.5% of the next 5000, correct?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
.
What happens if they sell over $100,000 ? Do they get paid 2% for each dollar over $100,000 ?
With the exception of the 'over $100,000 sales' ... try this formula : =IF(A1<=10000,A1*10%,IF(A1<=15000,1000+(A1-10000)*7.5%,IF(A1<=25000,2125+(A1-15000)*5%,3375+(A1-25000)*3%)))
The formula presumes the total amount of sales is listed in Cell A1.
Try
=SUMPRODUCT(--(A1>{0,10000,25000,50000,100000}),--(A1-{0,10000,25000,50000,100000}),{0.1,-0.025,-0.025,-0.02,-0.01})
Explanation in this thread
https://www.excelforum.com/excel-for...lculation.html
Life's a spreadsheet, Excel!
Say thanks, Click *
Thank you so much!! Really appreciated it!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks