how to calculate commissions

P

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
 
B

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.
 
R

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
 
R

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
 
P

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
 
R

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!
==============================================
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top