+ Reply to Thread
Results 1 to 11 of 11

Tiered Monthly Commission on YTD Sales Formula

  1. 01-23-2019, 11:42 AM #1
    Crh04h
    Crh04h is offline
    Registered User
    Join Date
    01-23-2019
    Location
    Florida
    MS-Off Ver
    Office 365
    Posts
    4

    Tiered Monthly Commission on YTD Sales Formula

    Hi All,

    I just joined the forum and appreciate any help you might be able to offer! We pay salespeople monthly on a tiered commission structure as a percentage of total sales YTD. I tried to solve with the appropriate IF statement but it doesn't prorate correctly for the month that the salesperson exceeds the commission tier. In my attached example the salesperson's sales exceed the tier of $60,000 in August and the formula incorrectly calculates all of that months commission at the higher 45% rather than prorating 4,000 at 40% and 4,000 at 45%, resulting in "monthly pay" of $3,600 rather than the correct $3,400. Any suggestions on how to correct?

    The IF statement I used: =IF(I9<$F$2,I8*$G$2,(I8*$G$3))

    Excel_Commission_SNIP.jpg

    Thanks!
    Chris
    Attached Files Attached Files
    Last edited by Crh04h; 01-23-2019 at 12:01 PM.
    Reply With QuoteRegister To Reply
  2. 01-23-2019, 12:14 PM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,464

    Re: Tiered Monthly Commission on YTD Sales Formula

    Hi and welcome
    In B10 enter =MIN(B$9,$F$2)*$G$2+MAX(0,MIN($F$3-$E$3,B$9-$E$3)*$G$3) and pull right
    Reply With QuoteRegister To Reply
  3. 01-23-2019, 12:25 PM #3
    Roel Jongman
    Roel Jongman is offline
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,483

    Re: Tiered Monthly Commission on YTD Sales Formula

    I choose to put the calculation in the YTD row as the tresholds are annual and calculate montly payment by substracting last mnth ytd from this month ytd

    In b11:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 01-23-2019, 12:32 PM #4
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,464

    Re: Tiered Monthly Commission on YTD Sales Formula

    @Roel
    Your formula does not take in to account that the max sales is 200000 (F3) ( if this is the OP's intention)
    Reply With QuoteRegister To Reply
  5. 01-23-2019, 12:36 PM #5
    Crh04h
    Crh04h is offline
    Registered User
    Join Date
    01-23-2019
    Location
    Florida
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Tiered Monthly Commission on YTD Sales Formula

    Quote Originally Posted by Pepe Le Mokko View Post
    Hi and welcome
    In B10 enter =MIN(B$9,$F$2)*$G$2+MAX(0,MIN($F$3-$E$3,B$9-$E$3)*$G$3) and pull right
    Thank you for your quick reply! When I plug that in the 10 row seems to be incorrectly increasing monthly. Looks like that is YTD pay instead? Excel_Commission_SNIP2.jpg
    Reply With QuoteRegister To Reply
  6. 01-23-2019, 12:38 PM #6
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,464

    Re: Tiered Monthly Commission on YTD Sales Formula

    Yes you're probably right
    Reply With QuoteRegister To Reply
  7. 01-23-2019, 12:39 PM #7
    Crh04h
    Crh04h is offline
    Registered User
    Join Date
    01-23-2019
    Location
    Florida
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Tiered Monthly Commission on YTD Sales Formula

    Quote Originally Posted by Roel Jongman View Post
    I choose to put the calculation in the YTD row as the tresholds are annual and calculate montly payment by substracting last mnth ytd from this month ytd

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

    Thanks for your reply! When I plug that in, I am getting this error...

    Excel_Commission_SNIP3.jpg
    Reply With QuoteRegister To Reply
  8. 01-23-2019, 12:41 PM #8
    Roel Jongman
    Roel Jongman is offline
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,483

    Re: Tiered Monthly Commission on YTD Sales Formula

    Copy it from the sheet, My local setting have ; as formula separator your system uses ,
    I forgot to change when posting here...

    also see comment Pepe. I did not take into account a max commission at 200.000 so mine runs endless without max amount commission
    Reply With QuoteRegister To Reply
  9. 01-23-2019, 12:48 PM #9
    Crh04h
    Crh04h is offline
    Registered User
    Join Date
    01-23-2019
    Location
    Florida
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Tiered Monthly Commission on YTD Sales Formula

    Quote Originally Posted by Roel Jongman View Post
    Copy it from the sheet, My local setting have ; as formula separator your system uses ,
    I forgot to change when posting here...

    also see comment Pepe. I did not take into account a max commission at 200.000 so mine runs endless without max amount commission

    Thanks! Something is going wrong when the tier is hit in August though... Excel_Commission_SNIP4.jpg
    Reply With QuoteRegister To Reply
  10. 01-23-2019, 12:50 PM #10
    shg
    shg is offline
    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: Tiered Monthly Commission on YTD Sales Formula

    A
    B
    C
    D
    E
    F
    1
    Sales
    Rate
    Delta
    2
    0
    40%
    40%
    		 D2: =C2 - N(C1)
    3
    60,000
    45%
    5%
    4
    5
    Month
    Sales
    Cumu
    Pay
    6
    		 Jan
    8,000
    8,000
    3,200
    		 D6: =SUMPRODUCT((C6 > $B$2:$B$3) * (C6 - $B$2:$B$3) * $D$2:$D$3) - SUM(D$5:D5)
    7
    		 Feb
    8,000
    16,000
    3,200
    8
    		 Mar
    8,000
    24,000
    3,200
    9
    		 Apr
    8,000
    32,000
    3,200
    10
    		 May
    8,000
    40,000
    3,200
    11
    		 June
    8,000
    48,000
    3,200
    12
    		 Jul
    8,000
    56,000
    3,200
    13
    		 Aug
    8,000
    64,000
    3,400
    14
    		 Sep
    8,000
    72,000
    3,600
    15
    		 Oct
    8,000
    80,000
    3,600
    16
    		 Nov
    8,000
    88,000
    3,600
    17
    		 Dec
    8,000
    96,000
    3,600
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  11. 01-23-2019, 01:12 PM #11
    Roel Jongman
    Roel Jongman is offline
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,483

    Re: Tiered Monthly Commission on YTD Sales Formula

    Quote Originally Posted by Crh04h View Post
    Thanks! Something is going wrong when the tier is hit in August though...
    The formula showing in the toolbar is not my formula, you changed some things.. My sheet has 3400 in I10 and 25800 in I11.
    So you have to undo whatever you changed in the formula.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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 structure formula
    By bwhite107 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-29-2018, 02:15 PM
  2. Need excel wiz for some help... Tiered Cumulative Commission Formula Thanks!
    By NeedHelp555 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-25-2018, 12:05 PM
  3. [SOLVED] Formula to calculate tiered commission/bonus
    By shrijan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-27-2017, 08:44 AM
  4. [SOLVED] Tiered commission formula past 6
    By trosasco23 in forum Excel General
    Replies: 1
    Last Post: 09-29-2014, 08:26 AM
  5. Tiered commission plan with new accounts plan paid monthly
    By Cmar in forum Excel General
    Replies: 0
    Last Post: 03-26-2014, 05:23 AM
  6. [SOLVED] Tiered commission plan paid monthly
    By Cmar in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-08-2014, 02:15 AM
  7. Formula for Tiered Sales Commission
    By philcaz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-01-2013, 06:12 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1