+ Reply to Thread
Results 1 to 5 of 5

Commission Schedule

  1. #1
    Registered User
    Join Date
    09-17-2008
    Location
    Akron, OH
    Posts
    2

    Commission Schedule

    I need formula assistance so as to calculate the correct commission based on the following schedule:


    $0 - $49,999 5%
    $50,000 - $99,999 10%
    $100,000 - $149,999 15%
    $150,000 - $199,999 20%
    $200,000 - $249,999 25%
    $250,000 - $299,999 30%
    $300,000 - $300,000 35%

    Commission are retroactive to dollar one. For example, if sales for the first period are $54,023.06 that would equate to a commission of $5,402.31 calculated as such:

    (49,999*.05) = $2,500
    (54,023.06 - 49,999) = $4,023 then: 4,023 *.10 = $402.31

    Since the salesperson is in the 10% tier, I need to go back and add an additional 5% on all sales up to that point which would be an additional $2,500 in this example. That would equate to (2,500 + 402.31 + 2,500) = $5,402.31.

    I'm having difficulty with the third period. It calculates 5% no matter where you are in the grid. Any help would be appreciated. File attached. Thx.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-11-2005
    Location
    London
    Posts
    259
    Suggest you simplify your table and use a VLOOKUP statement. The attached should give you some ideas.
    Attached Files Attached Files
    Martin Short

  3. #3
    Registered User
    Join Date
    09-17-2008
    Location
    Akron, OH
    Posts
    2

    Commission Schedule

    Thanks Martin. I do understand the LOOKUP function, I'm actually trying to put together a formula for cells D24:G24 that would correctly calculate the commission to be paid based on whatever sales value is in cells D9:G9.

  4. #4
    Forum Contributor
    Join Date
    04-11-2005
    Location
    London
    Posts
    259
    Hi

    A couple more formulae for you to ponder. This time I've still gone with VLOOKUPs, but added SUM functions to pick off the correct value.

    I've given you two different versions as I think I know what you're asking for - but just in case...

    HTH
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Hi
    For the commission of , say Quarter1, use
    =INDEX($F$13:$F$19;MATCH(D7;$D$13:$D$19;1))*D7
    and drag to the right for the other results
    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. commission function
    By needsomehelpher in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2008, 06:58 AM
  2. Commission Calculation Help
    By Maxsys in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-09-2008, 09:38 AM
  3. Commission schedule formula
    By ExecuZen I.T. in forum Excel General
    Replies: 4
    Last Post: 03-03-2008, 12:15 PM
  4. Calculating Accumulative Commission
    By outspokensa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2007, 04:01 AM
  5. help with a formula for working out commission earned...
    By neilmk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2006, 12:39 PM

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