+ Reply to Thread
Results 1 to 7 of 7

how to calculate commissions

  1. #1
    Peter
    Guest

    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

  2. #2
    bj
    Guest

    RE: how to calculate commissions

    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


  3. #3
    Ron Coderre
    Guest

    RE: how to calculate commissions

    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


  4. #4
    RagDyeR
    Guest

    Re: how to calculate commissions

    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" <Peter@discussions.microsoft.com> wrote in message
    news:8E0E5EED-27F4-4BC3-9B4F-AAA8AE656ABD@microsoft.com...
    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



  5. #5
    Peter
    Guest

    RE: how to calculate commissions

    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


  6. #6
    RagDyer
    Guest

    Re: how to calculate commissions

    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" <Peter@discussions.microsoft.com> wrote in message
    news:937CB091-6EA9-49CD-8A06-D5062E4A8684@microsoft.com...
    > 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



  7. #7
    Roger Govier
    Guest

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1