FORMULA TO CALCULATE VARYING SALES COMMISSIONS

  • Thread starter Thread starter Roadmap
  • Start date Start date
R

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
 
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
 
Back
Top