Calculate Commission based on running totals

**SebastienSoum** · 12-19-2011, 10:53 PM

Hi Excl Guru's

I was hoping you could point me in the direction to getting a sales commission claim form created. Our sales team earn commission, for 2 different groups of products (modules & licences). These commissions are broken down further into targets, for example:

For sales of modules, between $0 to $150K, I earn 6% commission
For sales of modules, between $150K to $300K, I earn 8% commission
For sales of modules, over 300K, I earn 12% commission.

In my sales commission form, I enter a line per order, in the following columns:
Customer
Description
Order Date
Lic/Mod (Licence or MOdule)
Value

I then need to calculate the commission I earned for each order.

What I was hoping on doing, with your help, its to calculate the amount of commission earned on each order based on my revenue and sales targets (6%, 8% or 12%).

I hope this makes sense, and all I'm hoping for right now is for someone to put me in the right direction to work this out.

Ps. I know a difficult point will be where a single sales order will take me over a %. For example, if my running sales total is $299K and I make a sales of $2K, this means I would earn 8% on $1K and 12% on the other $1K

**JBeaucaire** · 12-20-2011, 04:09 AM

Assuming the first "Value" is entered in E2, then this formula in F2 would give you the commission on that:

=IF(E2="", "", E2 * LOOKUP(E2,{0, 150000, 300000},{0.06, 0.08, 0.12}))

**JBeaucaire** · 12-20-2011, 04:15 AM

My apologies, I just realized from your final comment that the commissions are tiered, but not cumulative. You make 6% on the first 150k, 8% on the next 150k, then 12% after that. For that, the formula for a single cell to watch column E and calculate the tiered-non-cumulative commission would be:

=SUMPRODUCT(--(SUM(E:E)>{0,150000,300000}), SUM(E:E)-{0,150000,300000},{0.06,0.02,0.04})

**coachdbinmn** · 12-21-2011, 01:34 PM

jbeaucare, I applied the formula but I have a DECREASING commission schedule...It seems to not work properly...How would you tweak?

Here are the numbers...

12% paid on $0 - $25,000
10.26% paid on the next $25,000 - $50,000,
7.98% paid on the next $50,000 - $75,000,
3.27% paid on the next $75,000 - $100,000, and
2.28 paid on any amt over 100K

**Paul** · 12-21-2011, 01:35 PM

Hi Coach, welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.