Thread: sales commission formula required

Gentle folk, I have a new sale structure to put in place the commission is paid in the following way:
below 1500 zero commission
between 1501 and 3000, commission at 16%
between 3001 and 8000, commission at 23%
above 8001, commission paid at 30%
Ergo if you generate 5000 you would be paid 700 ie nothing for the first 1500, 16% of the second 1500 and 23% of the remaining 2000. ( I hope my maths is correct! )


I have tried to manipulate other solutions using sumproduct but my knowledge is poor, the formula I have tried manipulating is
=SUMPRODUCT( (A2 > {0,1500,3000,8000}) * (A2 - {0,1500,3000,8000}) * {0,0.16,0.23,0.3}). I prefer single line formula rather than lookups as staff will not be able to see commission rates easily.
If you whizzes could help I'd be more than happy also can anyone explain where I am going wrong with the formula above so I can learn a little?

uwatch

Welcome to Exceltip forum

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

I have moved your thread to the Excel Worksheet Functions

you need to adjust your rates such they "increment" -- ie between rate 1 and 2 is 16% increase, between rate 2 and rate 3 there is a 7% increase etc...

=SUMPRODUCT(--(A2>{0,1501,3001,8000}),A2-{0,1501,3001,8000},{0,0.16,0.07,0.07})

I would also use 1501,3001 etc... as you say % applies from 1501 upwards... you may choose to do some rounding with your A2 values accordingly.

No problem.

I built a little utility to explain how a given Sumproduct formula works... I've attached a file which shows how the commission result is calculated using this stepped methodology

Hopefully this will help shed light on how the calculation is processed and why incremental approach is required re: rates.

I know this is solved, but have attached a tiered sales commission template that you can modify (be careful with too much adaptation or the pivot table and nice formatting may no longer be functional).