1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Formula for a commission structure

Discussion in 'Business Applications' started by carls007, Jan 3, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. carls007

    carls007 Thread Starter

    Joined:
    Jan 3, 2012
    Messages:
    3
    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:

  2. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,934
    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
     
  3. carls007

    carls007 Thread Starter

    Joined:
    Jan 3, 2012
    Messages:
    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:

  4. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,934
    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
     
  5. carls007

    carls007 Thread Starter

    Joined:
    Jan 3, 2012
    Messages:
    3
    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!!
     
  6. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,934
    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%
     
  7. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1034331