+ Reply to Thread
Results 1 to 10 of 10

Sales Commission Formula

  1. #1
    Registered User
    Join Date
    11-14-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    21

    Post Sales Commission Formula

    I am trying to build a spreadsheet for my sales reps for their commission but get stuck on the percentage formulas. in this specific case im looking to do the following

    15 sales is monthly quota and should be 100% of the quota each sale is paid at $50.00 each at 100% rate. if the sales rep only sells 5 they would be paid whatever the % of he $ amount is of the 15 minimum quota. now if the sell anything over the 100% minimum quota the $50.00 rate should change to reflect the increase over 100%. Any help would be greatly appreciated. Thanks

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,732

    Re: Sales Commission Formula

    should be able to do with an IF statement , but would need to know the full rules, or attach an example spreadsheet

    something like
    =if( cell with sales quantity <15 , cell with sales quantity/15 *50 , if ( cell with sales quantity > 15 , calculate the over 100% rate, 750))
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Sales Commission Formula

    Try:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Change the SalesQty and SalesTarget references as necessary.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Registered User
    Join Date
    11-14-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Sales Commission Formula

    im sorry but im still alittle confused ill try to explain better.

    15 is minimum sales quota (but equals 100% of their sales quota)
    if they hit 15 sales they would be paid 100% of their commission for each one which is $50.00 each.
    if they fall below that quota for example they sold 7 i would need to figure out what % that 7 is of the 15 100% quota and adjust the pay to be that % of the $50.00 ( so if it happens to be 20% they pay would adjust to be 20% of $50.00 but if they sold 150% of their quota the pay would adjust to be 150% of $50.00

    i hope this better explains what im trying to do. thank you again for your help

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,732

    Re: Sales Commission Formula

    in your example
    6 sales would be at 40% of $50
    so 50*40% = $20
    *6 sales = $120
    and

    assuming 160%
    ie sold 24 items

    $50 * 160% = $80 per item
    24 * $80
    = $1920

    if so , then , the formula by

    OllyXLS

    is correct ?
    Last edited by etaf; 11-15-2013 at 10:33 AM.

  6. #6
    Registered User
    Join Date
    11-14-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Sales Commission Formula

    i will try to put a spreadsheet together using that formula and see if it works. if not ill post my sheet here and hopefully you can help if you see exactly what im trying to do. thank you so much for your help

  7. #7
    Registered User
    Join Date
    11-14-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Sales Commission Formula

    ok so i think that worked but how can i show the percentage that the commission is being paid at? i dont know how to show the spreadsheet on this post but below is just a copy and paste.


    monthly goal 15
    Commission $50.00
    sales 16

    Percentage Commission Paid At ???

    Commission $853.33
    Last edited by hitech357; 11-15-2013 at 01:23 PM.

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,732

    Re: Sales Commission Formula

    sales quantity / sales target *100

    16/15
    =1.0666667
    *100

    =106.6666%

  9. #9
    Registered User
    Join Date
    11-14-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Sales Commission Formula

    that works great just one more question when i change the cell to show percentage it shows up like this 10667% . how do i fix that to show 106%

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,732

    Re: Sales Commission Formula

    dont multiply by 100 , that will fix it

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula for sales commission structure
    By arkadd61 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-03-2013, 11:11 AM
  2. Sales Commission Formula
    By Numbda in forum Excel General
    Replies: 3
    Last Post: 03-19-2012, 06:05 PM
  3. Sales commission formula
    By Craig001 in forum Excel General
    Replies: 3
    Last Post: 09-21-2010, 04:59 AM
  4. Excel 2007 : sales commission formula
    By grace43 in forum Excel General
    Replies: 4
    Last Post: 11-17-2009, 07:50 PM
  5. Sales commission formula
    By shinymagic in forum Excel General
    Replies: 6
    Last Post: 11-17-2009, 01:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1