View Single Post
  #7   Report Post  
Roger Govier
 
Posts: n/a
Default how to calculate commissions

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


Peter wrote:
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

"Ron Coderre" wrote:


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:
InsertNameDefine
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


"Peter" wrote:


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