Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel Formula for a commission structure


(!)

carls007's Avatar
carls007 carls007 is offline
Junior Member with 3 posts.
THREAD STARTER
 
Join Date: Jan 2012
03-Jan-2012, 09:40 AM #1
Excel Formula for a commission structure
Hello, really stuggling here,

I need to calculate a commission based upon the following scenario.
(5% of the Total sales above 2000 upto 5000 and 18% on the remaining over 5000.)

I think i have the latter working ok?

I have attched a spreadsheet with the layout and formulas so far, if this can be used.


Any takers??

Thank you so much in advance,
Attached Files
File Type: xlsx COMMISSION 2012.xlsx (29.6 KB, 87 views)
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,536 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
03-Jan-2012, 10:00 AM #2
you can use a nested IF

IF( Cell > 5000 , Cell * 0.18 , If ( Cell > 2000 , Cell * 0.05 , 0 ) )

looking at the spreadsheet wasn't sure where the calculations where made

can you explain a little more please
carls007's Avatar
carls007 carls007 is offline
Junior Member with 3 posts.
THREAD STARTER
 
Join Date: Jan 2012
03-Jan-2012, 11:23 AM #3
Hello,

Thank you for helping me with this.

I am sorry its my explaining.

We would like to pay commisson as the following:

0 - 2k no percentage on sales
2k - 5k is at 5%
5k + is at 18%


So based on a sales person generating 10k

0 - 2k no percentage on sales
2k - 5k at 5% = 150
5k - 10k at 18% = 950 So that total payout would be 1050.


Based on how the spreadsheet stands at the moment its working out like this:

0-2k no percentage
2k - 8k at 5% = 400
5K - 10k 18% = 900 making the total payout 1300

(which means we are paying commission twice between 5k - 8k)

I have attached the spreadsheet again and highlighted in blue where your formulas are.

So this part of the formula =IF(B13>2000,B13*0.05,0) im thinkning would need to have some sort of condition in there so that it only works out a 5% commission from 2000 - 5000?

Is this possible?

is it my spreadsheet, is there an easier way to work this out other than how i have done this, i will need to record each sale and its amount etc and have a total sales and commission earned but if there is an easier layout im all ears!!

Many thanks again

Kind regards

carly
Attached Files
File Type: xlsx COMMISSION 2012 Help.xlsx (31.7 KB, 76 views)
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,536 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
03-Jan-2012, 12:33 PM #4
OK - I will work on the spreadsheet

in principle then
if the amount is greater than 5000 - I take the amount and subtract the 5000 - that part is paid at 18% - now because its greater then 5000 - we know we habe to also pay the 2000-5000 range - which is 3000 @ 5%

If the amount the condition for 5000 does not apply - then we test to see if over 2000 , if it is then its the cell -2000 which we then pay at 5%

IF( Cell > 5000 , ((Cell-5000) * 0.18)+(3000 *0.05) ) , If ( Cell > 2000 , ( (Cell-2000) * 0.05) , 0 ) )

hope I have explained OK - will now go and look at the spreadsheet
__________________
Wayne
Please let us know what the final solution was to any problem posted
carls007's Avatar
carls007 carls007 is offline
Junior Member with 3 posts.
THREAD STARTER
 
Join Date: Jan 2012
03-Jan-2012, 01:19 PM #5
Hey, i put that into the spreadsheet and that works!!

Think i have incorporated it in ok, i will send this to you tomorrow!

Thank you!!
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,536 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
03-Jan-2012, 01:22 PM #6
OK - havent had a chance to look at the spreadsheet so glad that worked out

so if they get 2001 they only get commission on the 1 correct
also I have not include =
so if it is 2000 that will not be included
and 5000 will be at 5%
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
commission, excel, formulas, spreadsheet

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑