+ Reply to Thread
Results 1 to 8 of 8

Tiered Compensation Formula- Help Creating Excel formula for tiers off of one sum

  1. #1
    Registered User
    Join Date
    04-09-2019
    Location
    CA
    MS-Off Ver
    OFFICE 2016
    Posts
    4

    Tiered Compensation Formula- Help Creating Excel formula for tiers off of one sum

    Hello All!

    I am new to excel, and i am working on the fly. I accidentally posted this in another thread already so i apologize.
    I need help figuring out a pricing tier for a compensation plan on EXCEL 2016.

    Pricing is as follows:

    <$1,000,000 x .0027= $2700
    $1,000,000-$2,000,000 x .0035= $3500
    $2,000,000-$3,000,000 x .0045= $4500
    $3,000,000-$4,000,000 x .0055= $5500
    >$4,000,000 x .007= $7000

    I have a sum of total dollar amounts but am at a loss for how to calculate for the tier dollar amounts per million in a single cell.

    EX. ($3,562,000) $2700+$3,500+$4,500+$3,091= $13,791


    Thank you!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Tiered Compensation Formula- Help Creating Excel formula for tiers off of one sum

    With your total in cell A1, you can use this formula:

    =IF(A1< 1000000,A1*0.0027,2700) + IF(A1< 2000000,MAX(0,A1-1000000)*0.0035,3500) + IF(A1< 3000000,MAX(0,A1-2000000)*0.0045,4500) + IF(A1< 4000000,MAX(0,A1-3000000)*0.0055,5500) + MAX(0,A1-4000000)*0.007

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Tiered Compensation Formula- Help Creating Excel formula for tiers off of one sum

    Unless there is limited space or file already >100mb,
    I suggest to make to more column or row.

    1. easy to check and understand by other people.
    2. it show more data for analysis (for example, how much of each tier paid).

    it also suggest to create a table (maybe in new sheet) for tier and link it to formula, it easy for amend in future.

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

    Re: Tiered Compensation Formula- Help Creating Excel formula for tiers off of one sum

    SUMPRODUCT is nice for this type of thing, e.g.:

    =SUMPRODUCT(--(A1>={0,1000000,2000000,3000000,4000000}),A1-{0,1000000,2000000,3000000,4000000},{0.0027,0.0008,0.001,0.001,0.0015})

    note the final array holds the marginal movement across the tiers

    with A1 = 3,562,000 you would get 13,791.

    if you store the tiers above, and relevant values, in a table you can replace the inline arrays -- e.g. if Data!A1:B5 held the first array, and last array {marginal movements}

    =SUMPRODUCT(--(A1>=Data!$A$1:$A$5),A1-Data!$A$1:$A$5,Data!$B$1:$B$5)

    and then very easy to adapt.
    Last edited by XLent; 04-10-2019 at 07:44 AM.

  5. #5
    Registered User
    Join Date
    04-09-2019
    Location
    CA
    MS-Off Ver
    OFFICE 2016
    Posts
    4

    Re: Tiered Compensation Formula- Help Creating Excel formula for tiers off of one sum

    Thank you i will try this right away!

  6. #6
    Registered User
    Join Date
    04-09-2019
    Location
    CA
    MS-Off Ver
    OFFICE 2016
    Posts
    4

    Re: Tiered Compensation Formula- Help Creating Excel formula for tiers off of one sum

    Works like a dream!

  7. #7
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Tiered Compensation Formula- Help Creating Excel formula for tiers off of one sum

    @Xlent


    ok, i understand what to be put.

    but why we have to use movement?

    {0.0027,0.0008,0.001,0.001,0.0015}
    Last edited by BoredWorker; 04-10-2019 at 10:30 PM.

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

    Re: Tiered Compensation Formula- Help Creating Excel formula for tiers off of one sum

    Quote Originally Posted by BoredWorker
    ...but why we have to use movement?
    because, logically, you are applying the rate of each tier to every relevant $ -- therefore you need only apply the "marginal" movement in each tier.

    if we use OP example, with outlined value of $3,562,000

    =SUMPRODUCT(--(3562000>={0,1000000,2000000,3000000,4000000}),3562000-{0,1000000,2000000,3000000,4000000},{0.0027,0.0008,0.001,0.001,0.0015})

    and evaluate the individual arrays

    =SUMPRODUCT({1,1,1,1,0},{3562000,2562000,1562000,562000,-438000},{0.0027,0.0008,0.001,0.001,0.0015})

    the above highlights that, initially, every $ within the 3562000 is going to be multiplied by / worth 0.0027
    in the next tier, with the exclusion of the first million {below threshold}, each $ will be worth an additional 0.0008 (so first 2m effectively @ 0.0035)
    in the next tier, with the exclusion of the first two million {below threshold}, each $ will be worth an additional 0.001 (so first 3m effectively @ 0.0045)
    etc...

+ 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: 9
    Last Post: 08-22-2018, 04:19 PM
  2. Advanced IF Function for Tiered Sales Compensation Plans
    By AJD2016 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 05-11-2016, 12:54 PM
  3. Replies: 7
    Last Post: 11-24-2015, 11:44 AM
  4. I need help creating a formula to pull back data and pricing based on tiers
    By bbeards2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-19-2015, 01:36 PM
  5. [SOLVED] need formula for tiered interest in excel???
    By katrinagidoc in forum Excel General
    Replies: 8
    Last Post: 08-11-2012, 08:20 AM
  6. If formula for compensation worksheet
    By chickieram63 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-12-2012, 07:18 PM
  7. Need help with sliding scale compensation formula
    By goosebug in forum Excel General
    Replies: 1
    Last Post: 06-22-2011, 02:40 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