Forum Discussion
Bonus calculation % achieved, % payout
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.
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?
