Forum Discussion
Bonus calculation % achieved, % payout
Hi -
I am trying to set up a formula that would calculate bonus payout based on achievement.
Lets say someone achieved 85% of their sales, their bonus is paid out at 25%.
But the payout is scaled in between tiers as well. So if someone achieves 90%, their payout is 62.5%.
If we achieve below 85% of our target the payout is 0%
If we achieve 85% of our target the payout is 25%
If we achieve 100% of our target the payout is 100%
If we achieve 110% of our target the payout is 120%
The previously used file has a formula a MILE long and it so hard to follow. There has to be a better way!
- Hello,
Kindly upload a sample file to get better help Actually, I figured it out. That example file would work if my given thresholds were the only possible payouts - but all the linear amts in between the thresholds are paid out too.
EX: So if someone achieves 90%, their payout is 62.5%.
That is the part that was tripping me up.
It's been a while since your post, but can you share the final solution for your problem?
Hi Patrick, can you please help to understand how this formula works.
VLOOKUP(A2,{0,0;0.85,0.25;1,1;1.1,1.2},2,1)
I was using If Logical functions.
It's VLOOKUP with a multi-dimensional array as the table array.
The array is the same as placing this in the sheet:
VLOOKUP is looking for its lookup values within ranges with 1 (Approximate match). Any value from 0 to <.85 returns 0. Any value .85 or greater but less than 1 returns .25, etc.
