?
Solved

Lookup Calculations

Posted on 2016-09-02
4
Medium Priority
?
64 Views
Last Modified: 2016-09-03
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
Comment
Question by:recycleaus
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:Ejgil Hedegaard
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

by:recycleaus
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 24

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points
ID: 41783238
0
 

Author Closing Comment

by:recycleaus
ID: 41783242
Thank you
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question