how to calculate commissions

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

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.

"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

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


"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

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


"Peter" <[email protected]> wrote in message
news:[email protected]...
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

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

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


"Peter" <[email protected]> wrote in message
news:[email protected]...
> 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:
> > 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
> >
> >
> > "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

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