+ Reply to Thread
Results 1 to 9 of 9

Formula to Calculate Scale of Commissions

  1. #1
    Registered User
    Join Date
    01-08-2016
    Location
    Georgia, Georgia
    MS-Off Ver
    MS 8
    Posts
    4

    Post Formula to Calculate Scale of Commissions

    So our owner made up a new way to do commissions, and it's taking me four days to manually calculate them and to be honest, I'm so afraid of human error, it's going to catch up at some point, so I'm hoping there is some sort of formula that can help!

    Our sales team gets a percentage based off the profit margin range, and I need a formula that can meet 8 conditions - which is where the "IF" conditions fail me.

    Basically all my "IF's" are:
    0-4 = 0%
    5-10 = 5%
    11-15 = 7%
    16-19 = 9%
    20-23 = 10%
    24-27 = 11%
    28-33 = 13%
    34-37 = 14%
    38-100 = 21%

    And that percentage times another field yields their commission.

    Help please??

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,990

    Re: Formula to Calculate Scale of Commissions

    To get %:

    =LOOKUP(A1,{0,5,11,16,20,24,28,34,38},{0,5,7,9,10,11,13,14,21})/100

    with A1 is profit.

  3. #3
    Forum Contributor
    Join Date
    12-16-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Formula to Calculate Scale of Commissions

    I created a video for creating sliding rate commission calculator, hopefully you can make it work for you

    https://www.youtube.com/watch?v=HaYjvE0OiDM

    http://www.easyexcelanswers.com

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,844

    Re: Formula to Calculate Scale of Commissions

    A lookup table is the best option.

    Look at VLOOKUP function

    Table with 0,5,11,16 etc in column A with % in B (0,5,7 ...)

    +VLOOKUP(D1,$A$1:$B$9,2,1) D1 profit margin

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula to Calculate Scale of Commissions

    With values in column A, enter this in column B formatted as %
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Using a table for VLOOKUP in $F$1:$F$9
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A
    B
    C
    D
    E
    F
    G
    1
    1
    0.00%
    0.00%
    0
    0%
    2
    10
    5.00%
    5.00%
    5
    5%
    3
    15
    7.00%
    7.00%
    11
    7%
    4
    20
    10.00%
    10.00%
    16
    9%
    5
    25
    11.00%
    11.00%
    20
    10%
    6
    30
    13.00%
    13.00%
    24
    11%
    7
    35
    14.00%
    14.00%
    28
    13%
    8
    40
    21.00%
    21.00%
    34
    14%
    9
    45
    21.00%
    21.00%
    38
    21%
    Last edited by newdoverman; 01-13-2016 at 12:19 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Registered User
    Join Date
    01-08-2016
    Location
    Georgia, Georgia
    MS-Off Ver
    MS 8
    Posts
    4

    Re: Formula to Calculate Scale of Commissions

    bebo021999, I was feeling AWESOME! that formula was good -- THEN I realized it didn't take into account rounding. For example, the report gave me 15.7333333 and when I "decrease decimal" so that a whole number shows, it calculated 7% for 15 instead of 9% for rounding up to 16.

    Any ideas?

  7. #7
    Registered User
    Join Date
    01-08-2016
    Location
    Georgia, Georgia
    MS-Off Ver
    MS 8
    Posts
    4

    Red face Re: Formula to Calculate Scale of Commissions

    I was feeling AWESOME! that formula was good -- THEN I realized it didn't take into account rounding. For example, the report gave me 15.7333333 converted into Excel, and when I use the "decrease decimal" icon so that a whole number (rounded up) shows, it calculated 7% for 15 instead of 9% for rounding up to 16.

    Any ideas?

    PS: Thanks for the quick and super simple formula! If I can get the rounding corrected, I'll be Golden!!!

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula to Calculate Scale of Commissions

    What I gave you (both examples) in msg#5 don't round.

    What rounding are you doing? I don't see any reason to do rounding in the problem that you posed.

  9. #9
    Registered User
    Join Date
    01-08-2016
    Location
    Georgia, Georgia
    MS-Off Ver
    MS 8
    Posts
    4

    Re: Formula to Calculate Scale of Commissions

    Eeep! I figured it out.

    To explain, the database generates a report that I past into excel. So my numbers look more like this:

    15.73333
    19.81552
    20.77781
    30.67675
    37.79472

    I use the "decrease decimal" icon under the "Number" tab in the ribbon until I get a clean, rounded value, ie:

    round to 16
    round to 20
    round to 21
    round to 31
    round to 38

    The lookup formula I'm using =LOOKUP(I9,{0,5,11,16,20,24,28,34,38},{0,5,7,9,10,11,13,14,21})/100 was calculating the percentages for:

    15 (instead of 16)
    19 (instead of 20)
    20 (instead of 21)
    30 (instead of 31)
    37 (instead of 38)

    So I changed the formula to:
    =LOOKUP(I9,{0,4.49,10.49,15.49,19.49,23.49,27.49,33.49,37.49},{0,5,7,9,10,11,13,14,21})/100

    It's working perfectly!!!

    Thanks to ALL of you for being so quick and helpful!

+ 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. Formula to calculate sales commissions based on % discounted
    By Doug S in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-05-2015, 06:42 PM
  2. Worksheet Attached - Formula needed to calculate commissions vs date and Emp ID #
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-02-2013, 12:36 AM
  3. [SOLVED] Need formula to calculate commissions for employees based on the month earned on 2 sheets
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-02-2013, 12:09 AM
  4. Formula to calculate/compare changes in 1 to 10 scale!......
    By cityguy10005 in forum Excel General
    Replies: 2
    Last Post: 01-07-2010, 10:03 PM
  5. Excel 2007 : Calculate commissions
    By jaybees12 in forum Excel General
    Replies: 3
    Last Post: 05-16-2009, 07:31 PM
  6. how to calculate commissions
    By Peter in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2005, 04:10 AM
  7. [SOLVED] Help With Formula To Calculate Commissions
    By Smonczka in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-04-2005, 11:50 AM

Tags for this Thread

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