Results 1 to 4 of 4

Formula to calculate percentage based on achievement gates

This is a discussion on Formula to calculate percentage based on achievement gates within the Excel Questions forums, part of the Question Forums category; Hi team. I consider myself a fairly competent user but am baffled by this problem. I work in an industry ...

  1. #1
    spr
    spr is offline
    New Member
    Join Date
    Jul 2010
    Posts
    5

    Default Formula to calculate percentage based on achievement gates

    Hi team. I consider myself a fairly competent user but am baffled by this problem.

    I work in an industry where we are compensated by suppliers on volume business (i.e. a rebate) based on achievement.
    There are different gates that need to be achieved to work out how much we are payed in rebate for a particular period.

    i.e. we have a target of 100.
    if we achieve 80% we are payed a 1% rebate
    if we achieve 90% we are payed a 2% rebate
    if we achieve 100% we are payed a 3% rebate
    and so on - to a cap of 120% where we are payed 5% rebate.

    I am trying to calculate the payout figure based on the banding and just stuck with this one! any help appreciated.


  2. #2
    Board Regular shemayisroel's Avatar
    Join Date
    Sep 2008
    Location
    Sydney - Australia
    Posts
    1,865

    Default Re: Formula to calculate percentage based on achievement gates

    Hi & Welcome,


    Something like this as a base model?

    You can use he solution in F2 to multiply it against the rebate about...


    ABCDEF
    1MINMAXREBATE RESULTREBATE
    20%80%1% 98%3%
    380%90%2%
    490%100%3%
    5100%120%4%
    6120% 5%

    Spreadsheet Formulas
    CellFormula
    F2=INDEX(C2:C6,MATCH(E2,A2:A6,1))
    enercheenhologoskaihoogosenprostontheonkaitheosenhologosnarchhnoogovkailogohnprvtonqeonkaiqeovhnologov

  3. #3
    spr
    spr is offline
    New Member
    Join Date
    Jul 2010
    Posts
    5

    Default Re: Formula to calculate percentage based on achievement gates

    Thanks for that - however the complexity grows.

    the payment is tiered - i.e. we are paid on the 0-80% + the 80-90 + 90-100 + 100-120 as individual payments.

    so in a simple model.

    90-100% = 5% rebate
    100%+ = 10% rebate

    Target of 100. Achieve 120

    so 100/120 is paid @ 5% = $5
    20 is paid @ 10% = $2

    there are 5 steps in the model I currently have.

  4. #4
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default Re: Formula to calculate percentage based on achievement gates

    Two plus two equals five for large values of two.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com