# Lookup Calculations

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:
Min: \$2.00
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.

Troy
Sales-Commissions.xlsx
###### Who is Participating?

Commented:
0

Commented:
Check if attached does what you expect.
The formula in B36 is awful long, so I have added a table below, to show how the values are calculated.
Perhaps you should use the result from that instead, because it will be much easier to maintain.
Sales-Commissions.xlsx
0

Author Commented:
Ejgil. Thanks for that, looks like it working to me and yeh that formula in B36 looks huge as it is so it must have been massive.

Just one other thing, I need to move cell I5, which was the product selector, due to the text not fitting in but in the process I have killed the calculations. Would you please be able to correct this for me?

Thanks again
Sales-Commissions.xlsx
0

Author Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.