Formula to calculate percentage based on achievement gates

Thanks:  0
Likes:  0

# Thread: Formula to calculate percentage based on achievement gates

1. ## 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. ## 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...

 A B C D E F 1 MIN MAX REBATE RESULT REBATE 2 0% 80% 1% 98% 3% 3 80% 90% 2% 4 90% 100% 3% 5 100% 120% 4% 6 120% 5%

 Cell Formula F2 =INDEX(C2:C6,MATCH(E2,A2:A6,1))

3. ## 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.

## User Tag List

#### Posting Permissions

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