how to calculate commissions

[Peter]

How do I calculate commissions for employees if the following were to occur:

If an employee has 0-100 dollars in sales, there is no commission.
If an employees has 100-200 dollars in sales, there is a 5% commission.
If an employee has 200-300 dollars in sales, there is a 10% commission.
If an employee has 300+ dollars in sales, there is a 15% commission.

Peter

 

[bj]

try in your commision column
=Sales*if(Sales>300,.15,if(Sales>200,.10,if(Sales>100,.05,0)))
where Sales is the cell with the slaes quantity.

 

[Ron Coderre]

You might want to use a lookup table:

On a separate worksheet (in the same wkbk) build this table:
Col_A Col_B

Base Pct
$0 0%
$100 5%
$200 10%
300 15%

Select those cells and name them:
Insert>Name>Define
Name: LU_ComRate

To calculate commission percent:
Select sheet with amounts.
Assuming the commission base amount is in Cell A1:
B2: =VLOOKUP(A1,LU_ComRate,2,1)

Does that help?

••••••••••
Regards,
Ron

 

[RagDyeR]

The question now ... is the commission paid on the *entire* sales amount,
OR
Is the 5% paid on *only* the first 100 to 200 dollars,
And 10% paid on *only* the 200 to 300 dollars sales amount ... etc.?

If this be the case, check out John's link at:

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


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


How do I calculate commissions for employees if the following were to occur:

If an employee has 0-100 dollars in sales, there is no commission.
If an employees has 100-200 dollars in sales, there is a 5% commission.
If an employee has 200-300 dollars in sales, there is a 10% commission.
If an employee has 300+ dollars in sales, there is a 15% commission.

Peter

 

[Peter]

What if the commission was based on only a portion of the sales. For ex: the
person earns a 10% commission on sales between $200-$300, and 15% on sales
between $300-$400?

Peter

 

[RagDyer]

Did you not see my earlier post which addressed this exact situation?

Once again, check out this link for a procedure:

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


--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Roger Govier]

Hi Peter

Try
=MAX(0,A1-100)*5%+MAX(0,A1-200)*5%+MAX(0,A1-300)*5%

Dependent upon whether you want the hundreds to be part of the higher bands,
or lower bands, you may need to adjust the subtraction to 99, 199, 299.

Regards

Roger Govier