FORMULA TO CALCULATE VARYING SALES COMMISSIONS

[Roadmap]

I pay an employee a base salary of $800 (based on sales of 0-15,000)
plus:

I want to pay 7% commission on the excess of the amount greater than
15,000 or less than 20,000;
I want to pay 8% commission on the excess of the amount greater than
20,000 and less than 25,000;
I want to pay 10% commission on the excess of the amount greater than
25,000 and less than 30,000

 

[T. Valko]

See this:

http://mcgimpsey.com/excel/variablerate.html

 

[Shane Devenshire]

Hi,

Suppose cell B1 contains the the sales figures

=MIN(MOD(B1,15000),5000)*0.07+MIN(MOD(B1,20000),5000)*0.08+MIN(MOD(B1,25000),5000)*0.1

This represents the commission only. And note there is no reward for any
sales above 30,000 not even 10%.

If this helps, please click the Yes button.

Cheers,
Shane Devensire