Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Lookup Calculations

Posted on 2016-09-02
Medium Priority
62 Views
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
0
Question by:recycleaus
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 23

Expert Comment

ID: 41783224
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 Comment

ID: 41783229
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

LVL 23

Accepted Solution

Ejgil Hedegaard earned 2000 total points
ID: 41783238
0

Author Closing Comment

ID: 41783242
Thank you
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month10 days, 19 hours left to enroll