+ Reply to Thread
Results 1 to 4 of 4

Calculation to work out tiered commission result

  1. #1
    Registered User
    Join Date
    03-30-2021
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    7

    Calculation to work out tiered commission result

    Hey,

    Looking for help with a formula to to work out a new commission plan in my work from looking at our actual total billed

    The new structure is as follows. if I bill

    $60,000 - $90,000 = 20%
    $90,001 - $140,000 = 35%
    $140,001 - $175,000 = 45%
    $175,001 + = 50%

    To clarify, 60k is threshold. If I bill between 60 -90k I get 20% of the value over 60k. I.e. if I bill 70k I get 20% of 10k = 2k.

    Where it gets complex is working out the remaining teirs - If I bill say 145k I recieve 20% of $30k (difference between 60-90), 35% of 50k (differece between 90-140) and then 45% of 5k.

    Is there a formula I could use to work out the above commission scheme to share with my team? Any help would be appreciated.

    Thanks,
    Michael

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Calculation to work out tiered commission result

    If you only have a few tiers you can use an amalgam of MIN + MAX calcs to aggregate each tier however, a SUMPRODUCT approach is a little more flexible, e.g.:

    =SUMPRODUCT((A1>{0,60000,90000,140000,175000})+0,A1-{0,60000,90000,140000,175000},{0,0.2,0.15,0.1,0.05})

    where A1 holds the amount - e.g. 145k ... the above would return 25750
    Last edited by XLent; 03-30-2021 at 06:21 AM.

  3. #3
    Registered User
    Join Date
    03-30-2021
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Calculation to work out tiered commission result

    Wow, that worked an absolute treat! It's completely beyond my comprehension what you've done there but it's given me exactly what I need so THANKYOU! Have a great evening!

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Calculation to work out tiered commission result

    glad it helped -- the approach is outlined in the below link: http://www.mcgimpsey.com/excel/variablerate.html

+ 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 pay
    By BlueStreamCS in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-03-2018, 11:46 AM
  2. Tiered Commission Structure
    By emily.kell in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-18-2014, 10:59 PM
  3. Tiered Commission Calculation
    By Rushmore in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-01-2012, 12:18 AM
  4. Tiered Commission
    By rock_chalk35 in forum Excel General
    Replies: 7
    Last Post: 06-08-2012, 01:39 PM
  5. Tiered Commission calculation Macro
    By mlaracuente in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-19-2012, 05:39 AM
  6. Tiered Pricing Commission Calculation
    By kludge in forum Excel General
    Replies: 2
    Last Post: 04-13-2011, 06:10 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1