A excel sheet that able to figure varying commissions and return a commission total
I have a newly added responsibility at work and one of my new tasks is figuring out commissions for our outside sale team. We have 5 different percentage rates we pay out depending on the type of sale and amount of sale. The person now appears to be doing a rather tedious method of using a calculator. What I tried to find searching google mainly focused on sliding commission rate. What I would like is a macro that has some way of me entering in dollar amounts into these rates. 3%, 4%, 5%, 6% and 7%. What would be great is a cumulative commission total. So I could see me entering 3 totals for the 3%, 6 on the 5% and 30 into the 7%. I hope this is enough information. Please ask any question you may need.
As always many thanks for your help on this
As always many thanks for your help on this
I agree with Rob, formulas will do the trick. Maybe with a Lookup Table to determine the correct rates. I think I have an example that I vcan attach later.
ASKER
Here is a very ruff file. The problem is I don't know how to be able to add varied sales under each % and keep the totals dynamic
Commisions.xlsx
Commisions.xlsx
Can you explain why each of those sales amounts goes into the % bracket that you've put them in?
ASKER
It based on the sale type. Certain sales are categorized at different rates and also total sales amount totals also can determine I will be hand in-putting the amounts. I would imaging that the percentages could be columns as I think about it
Can you provide a list of the conditions? And add a Sales Type to the values already provided.
Sales Type = ##%
Sales Type = ##%
ASKER
Here's an updated file. I was hoping for something more elegant
Commisions.xlsx
Commisions.xlsx
Currently converted your file to look like the attached.
With the Type column populated, the Rate column can then be a lookup on a table, off to the right in the sample but can easily be on another sheet.
Commisions.xlsx
With the Type column populated, the Rate column can then be a lookup on a table, off to the right in the sample but can easily be on another sheet.
Commisions.xlsx
Here's an example file for VLOOKUP which explains how to use VLOOKUP for calculating commissions or discounts
VLOOKUP.xlsx
VLOOKUP.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you can upload a sample workbook with some data and the rules for when the different commission rates are paid we can take a look.