A excel sheet that able to figure varying commissions and return a commission total

Dan Purcell used Ask the Experts™
on
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
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Finance Analyst

Commented:
I doubt you would need a macro, it could probably be done with formulas.

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.
Group Finance Manager

Commented:
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.
Manager

Commented:
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
Finance Analyst

Commented:
Can you explain why each of those sales amounts goes into the % bracket that you've put them in?
Manager

Commented:
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
Finance Analyst

Commented:
Can you provide a list of the conditions? And add a Sales Type to the values already provided.

Sales Type = ##%
Manager

Commented:
Here's an updated file. I was hoping for something more elegant
Commisions.xlsx
Finance Analyst

Commented:
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
Group Finance Manager

Commented:
Here's an example file for VLOOKUP which explains how to use VLOOKUP for calculating commissions or discounts
VLOOKUP.xlsx
Manager
Commented:
I was hoping there was something other that what I was doing but I'll just keep using my old sheet.