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

Dan Purcell
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance 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.
Roy CoxGroup 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.
Dan PurcellManager

Author

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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rob HensonFinance Analyst

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

Author

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
Rob HensonFinance Analyst

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

Sales Type = ##%
Dan PurcellManager

Author

Commented:
Here's an updated file. I was hoping for something more elegant
Commisions.xlsx
Rob HensonFinance 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
Roy CoxGroup 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.

Thanks for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial