Hi, I am hoping somewhere can help with lookup calculations I am trying to put into my spreadsheet as it is beyond my Excel skill set.
The workbook is for the calculation of potential sales rep commissions. There is a base rate and 4 bonus rates. The base rate is only paid if the ASP (average sales price) is above the minimum $ value (column B). Then bonuses are paid on a scaled basis again relating back to the ASP. It will be clear once the workbook is viewed but to clarify I will detail how the scaled bonuses should work.
Base commission, if minimum ASP is reached, is 2% and then bonus rates are:
Bonus 1: 12.5%
Bonus 2: 25.0%
Bonus 3: 37.5%
Bonus 4: 50.0%
If we take batteries as the example:
Rate 1: $2.20
Rate 2: $2.40
Rate 3: $2.60
Rate 4: $2.80
If the ASP is $2.71/kg then the bonus amount should be $0.15625/kg based on the below calculations:
Min: $2.00 * 2%: $0.04000
Rate 1: $0.20 * 12.5% = $0.02500
Rate 2: $0.20 * 25.0% = $0.05000
Rate 3: $0.11 * 37.5% = $0.04125
There are 2 highlighted areas I need help with in the attached workbook.
1. The calculator which is highlight in Cell B36
2. The income table which is highlighted in Cells J5:N12 (table is based on product selected in Cell I5)
Thanks to anyone who can help.