Sales Calculations

Hi Please Help!

I am trying to calculate what a salesman gets paid for total sales of $12,500. He makes:

20% on the first 5000
25% from 5001-10000 and
30% on the amount over 10,000

These calculations then need to be added up to get the final commission amount for the month, and I want to be able to change the 12,000 amount.

Please help!

Try the following...

=SUMPRODUCT(--(A1>{0,5000,10000}),(A1-{0,5000,10000}),{0.2,0.05,0.05})

...where A1 contains the sales amount.

Hope this helps!

Originally Posted by Rainbow4

Hi Please Help!

I am trying to calculate what a salesman gets paid for total sales of $12,500. He makes:

20% on the first 5000
25% from 5001-10000 and
30% on the amount over 10,000

These calculations then need to be added up to get the final commission amount for the month, and I want to be able to change the 12,000 amount.

Please help!

This is one alternative:
=CHOOSE(1--(A1>=0)--(A1>5000)--(A1>10000),0,A1*20%,5000*20%+(A1-5000)*25%,5000*20%+5000*25%+(A1-10000)*30%)
Though, I like Domenic's solution.

Ola Sandstrom

Note:
(A1>=0) --> True. The -- converts True, first to -1 then to +1. --(A1>=0) --> +1

Thanks very much for all of your help. I really appreciate it.

Rainbow