+ Reply to Thread
Results 1 to 6 of 6

Tiered Bonus Structure template

  1. 10-01-2018, 05:24 PM #1
    dooz83
    dooz83 is offline
    Registered User
    Join Date
    10-01-2018
    Location
    charlotte, NC
    MS-Off Ver
    365
    Posts
    1

    Question Tiered Bonus Structure template

    Hi I'm trying to find a good way to create an formula to calculate a bonus off a tiered structure. if the # of houses is 250 - how can i get it to calculate the first 200 * $10 and the last 50*$15. etc... Any help anyone can give would be great!! Thanks in advance


    #houses $per house

    0-200 $10
    201-400 $15
    401-600 $20
    601-above $30
    Reply With QuoteRegister To Reply
  2. 10-01-2018, 05:39 PM #2
    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 Bonus Structure template

    A
    B
    C
    D
    1
    Houses
    Comm
    Delta
    2
    0
    $10
    $10
    		 C2: =B2-N(B1)
    3
    200
    $15
    $5
    4
    400
    $20
    $5
    5
    600
    $30
    $10
    6
    7
    Houses
    Comm
    8
    100
    $1,000
    		B8: =SUMPRODUCT((A8 > $A$2:$A$5) * (A8 - $A$2:$A$5) * $C$2:$C$5)
    9
    200
    $2,000
    10
    300
    $3,500
    11
    400
    $5,000
    12
    500
    $7,000
    13
    600
    $9,000
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  3. 10-01-2018, 05:40 PM #3
    jtakw
    jtakw is offline
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Tiered Bonus Structure template

    Hi,

    You can set it up this way:

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    F
    G
    1
    		 Greater than To Bonus Delta # Houses Bonus
    2
    0
    200
    10
    10
    250
    2750
    3
    200
    400
    15
    5
    401
    5020
    4
    400
    600
    20
    5
    175
    1750
    5
    600
    30
    10
    750
    13500
    Sheet: Sheet87

    Excel 2016 (Windows) 64 bit
    G
    1
    		 Bonus
    2
    =SUMPRODUCT((A$2:A$5<F2)*(F2-A$2:A$5)*D$2:D$5)
    Sheet: Sheet87
    Reply With QuoteRegister To Reply
  4. 10-01-2018, 05:41 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Tiered Bonus Structure template

    with the value in Q2...
    =IF(Q2<200,10,IF(Q2<400,10+15,IF(Q2<600,25+20,45+30)))

    edit: Mis-read the question, I need to * not +
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 10-01-2018, 05:45 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Tiered Bonus Structure template

    OK, I came up with this...
    =IF(Q2<200,Q2*10,IF(Q2<400,2000+5*Q2,IF(Q2<600,5000+5*Q2,9000+Q2*5)))
    Reply With QuoteRegister To Reply
  6. 10-01-2018, 07:08 PM #6
    jtakw
    jtakw is offline
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Tiered Bonus Structure template

    Another option for OP if he/she doesn't want to set up the table as shg and I suggested above:

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    F
    7
    		 From To Bonus # Houses Bonus
    8
    0
    200
    10
    250
    2750
    9
    201
    400
    15
    401
    5020
    10
    401
    600
    20
    175
    1750
    11
    601
    30
    750
    13500
    Sheet: Sheet87

    Excel 2016 (Windows) 64 bit
    F
    7
    		 Bonus
    8
    =MIN(F2,B$8)*C$8+MEDIAN(0,F2-B$8,B$9-B$8)*C$9+MEDIAN(0,F2-B$9,B$10-B$9)*C$10+MAX(0,F2-B$10)*C$11
    Sheet: Sheet87
    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 bonus
    By kimbdalr in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-31-2018, 04:20 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 (not percentage)
    By pglaeser in forum Excel General
    Replies: 5
    Last Post: 11-17-2014, 04:26 PM
  4. Tiered bonus structure
    By csheils79 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-20-2014, 10:29 AM
  5. Help! Tiered sales/bonus structure...
    By cubby777 in forum Excel General
    Replies: 1
    Last Post: 03-29-2014, 06:21 AM
  6. Cumulative Tiered Bonus Structure
    By dpleventhal in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-11-2011, 11:58 AM
  7. Tiered Bonus Structure
    By fwendly in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-18-2009, 04:14 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