Forum Discussion
Need help with a Tiered Bonus Calculation
HI all,
I am trying to create a formula that will return a payout from a table based on goal achievement. For example, if the associate achieves between 100 - 102.99% to goal, they receive a payout of $3,000, if they achieve between 103% and 105.99% to goal, they receive a payout of $3,500, etc..
thanks for your help in advance!
Set up the payout table as shown in columns G, H, and I, where the ranges for each tier are specified.
Use an INDEX and MATCH formula in the Bonus Payout column (e.g., column E) to look up the correct payout based on the % to Goal value.
Use the following formula in cell E3 (for the first associate) to get the correct payout:
=INDEX($I$3:$I$8, MATCH(D3, $G$3:$G$8, 1))
Make sure the ranges in the Low column (G3:G8) are sorted in ascending order.
Range Adjustments: Adjust $I$3:$I$8, $G$3:$G$8, and $H$3:$H$8 as needed if your data is in different rows or columns.
Here is an example of what i am looking for
Set up the payout table as shown in columns G, H, and I, where the ranges for each tier are specified.
Use an INDEX and MATCH formula in the Bonus Payout column (e.g., column E) to look up the correct payout based on the % to Goal value.
Use the following formula in cell E3 (for the first associate) to get the correct payout:
=INDEX($I$3:$I$8, MATCH(D3, $G$3:$G$8, 1))
Make sure the ranges in the Low column (G3:G8) are sorted in ascending order.
Range Adjustments: Adjust $I$3:$I$8, $G$3:$G$8, and $H$3:$H$8 as needed if your data is in different rows or columns.Thank you! this is perfect
