Forum Discussion
Need help with a Tiered Bonus Calculation
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