+ Reply to Thread
Results 1 to 5 of 5

Formula to calculate tiered commission/bonus

  1. #1
    Registered User
    Join Date
    10-13-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2013
    Posts
    52

    Formula to calculate tiered commission/bonus

    Hi all excel gurus,

    Trying to work out tiered bonus for my project. I have two scenarios with different criteria to calculate bonuses. Could you please assist based on attached?

    Thank you.

    S
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Formula to calculate tiered commission/bonus

    In F4 try

    =SUMPRODUCT(--(F3>{0,58000000,63000000,68000000}),--(F3-{0,58000000,63000000,68000000}),{0,0.025,-0.0025,-0.0025})

    in F21 try

    =SUMPRODUCT(--(F20>{0,69000000,74000000}),--(F20-{0,69000000,74000000}),{0.025,-0.005,-0.005})
    Last edited by 63falcondude; 06-14-2017 at 09:10 PM.

  3. #3
    Registered User
    Join Date
    10-13-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2013
    Posts
    52

    Re: Formula to calculate tiered commission/bonus

    Thank you.

    Just trying to follow the formula, is there a way i can link the hard coded $ in the formula to the cells that they relate to (i tried linking but to to luck). Also, dont seem to follow the rates you've used.

    S

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Formula to calculate tiered commission/bonus

    That complicates the formula quite a bit but here it is. I changed the tables to work with the formula.

    The rates that you are referring to are the difference of the rates. See attachment for clarification.

    Source: https://www.mrexcel.com/forum/excel-...cing-grid.html
    Attached Files Attached Files

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Formula to calculate tiered commission/bonus

    Thanks for the rep!

    If that solved your question, please mark this thread as SOLVED.

+ 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. Replies: 4
    Last Post: 02-01-2017, 08:13 PM
  2. [SOLVED] Tiered Bonus Formula
    By gbahmad in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-21-2016, 04:52 PM
  3. Tiered bonus formula
    By Dark_Legion in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2015, 09:41 PM
  4. Formula for calculating a tiered quarterly bonus payout
    By kbroom13 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-28-2015, 06:51 PM
  5. [SOLVED] Tiered commission formula past 6
    By trosasco23 in forum Excel General
    Replies: 1
    Last Post: 09-29-2014, 08:26 AM
  6. Formula for Tiered Sales Commission
    By philcaz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-01-2013, 06:12 PM
  7. Need a formula to figure out a pay rate for a tiered bonus program
    By cellinol91 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-24-2013, 10:37 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