Search Search for: Business ApplicationsAll Forums

# Excel Formula for a commission structure

 carls007
Junior Member with 3 posts.

Join Date: Jan 2012
03-Jan-2012, 08: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
 COMMISSION 2012.xlsx (29.6 KB, 103 views)
 etaf   (Wayne)
Moderator with 55,318 posts.

Join Date: Oct 2003
Location: Surrey, UK
03-Jan-2012, 09:00 AM #2
you can use a nested IF

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

can you explain a little more please
 carls007
Junior Member with 3 posts.

Join Date: Jan 2012
03-Jan-2012, 10: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
 COMMISSION 2012 Help.xlsx (31.7 KB, 94 views)
 etaf   (Wayne)
Moderator with 55,318 posts.

Join Date: Oct 2003
Location: Surrey, UK
03-Jan-2012, 11:33 AM #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
Junior Member with 3 posts.

Join Date: Jan 2012
03-Jan-2012, 12: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   (Wayne)
Moderator with 55,318 posts.

Join Date: Oct 2003
Location: Surrey, UK
03-Jan-2012, 12:22 PM #6

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%
 techguy.org/1034331
As Seen On

WELCOME TO TECH SUPPORT GUY!

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

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)