+ Reply to Thread
Results 1 to 4 of 4

Formula for Tiered Sales Commission

  1. #1
    Registered User
    Join Date
    10-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    Formula for Tiered Sales Commission

    Ok my problem is this but:
    Sales Commissions paid on a tiered structure
    `Sales 0 - 1,250,000 = NO Commission Paid
    `Sales 1,251,000 - 1,500,000 = 4% of the amount over 1,250,000 (so if sales were 1,400,000, commission would be 6000)
    `Sales 1,501,000 - 1,750,000 = 5% of the amount over 1,500,000 and 4% of the full 250,000 from above (so if sales were 1,600,000, commission would be 10000+5000=15000)
    and so on with the follow 2 tiers as well
    `Sales 1,751,000 - 2,000,000 = 6% of the amount over 1,750,000 and 5% of the full 250,000 from above and 4% of the full 250,000 above that.
    `Sales 2,000,000 and above = 7% of any amount over 2,000,000 and 6% of the full 250,000 above and 5% of the full 250,000 from above that and 4% of the full 250,000 above that.

    I really hope this makes sense, it has been done manually bu could REALLY use a formula to save a lot of time.

    I have manually done the formulas per cell but I would like to do one that can be copied to all others so if any part changes either the tier or % I only have to update in one place.
    Total Sales - $1,680,000
    End Result (Commission Paid) - $19,000
    Formula Used - =(250000*0.04) + ((M16-1500000)*0.05)
    M16 is the total sales amount

    I am happy to give more examples if needed.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula for Tiered Sales Commission

    A
    B
    C
    D
    1
    Sales
    Comm Rate
    Diff Rate
    2
    $ -
    0%
    0%
    C2: =B2-N(B1)
    3
    $ 1,250,000
    4%
    4%
    4
    $ 1,500,000
    5%
    1%
    5
    $ 1,750,000
    6%
    1%
    6
    $ 2,000,000
    7%
    1%
    7
    8
    Sales
    Commission
    9
    $ 1,400,000
    $ 6,000
    B9: =SUMPRODUCT((A9>$A$2:$A$6) * (A9-$A$2:$A$6) * $C$2:$C$6)
    10
    $ 1,600,000
    $ 15,000
    11
    $ 1,680,000
    $ 19,000
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Formula for Tiered Sales Commission

    Oh my!!!! Thank you so much, you have made my life so much easier!! Now I can make changes to the tiers or % or sales on the fly to get instant new numbers for the 10 years!! Amazing.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula for Tiered Sales Commission

    You're welcome.

+ 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. Tiered Commission Calculation
    By Rushmore in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-01-2012, 12:18 AM
  2. Tiered Commission
    By rock_chalk35 in forum Excel General
    Replies: 7
    Last Post: 06-08-2012, 01:39 PM
  3. Sales Commission Formula
    By Numbda in forum Excel General
    Replies: 3
    Last Post: 03-19-2012, 06:05 PM
  4. Sales commission formula
    By Craig001 in forum Excel General
    Replies: 3
    Last Post: 09-21-2010, 04:59 AM
  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