+ Reply to Thread
Results 1 to 4 of 4

Formula to calculate sales commissions based on % discounted

  1. #1
    Registered User
    Join Date
    01-05-2015
    Location
    Michigan
    MS-Off Ver
    Office 365
    Posts
    2

    Formula to calculate sales commissions based on % discounted

    I am trying to create a formula to calculate sales commissions. I have four ranges based on the amount of a discount that was given and then the level of commission earned.
    My table looks like this:

    18-20% discount then a 3% commission is earned
    16-17% discount then a 3.2% commission is earned
    14-15% discount then a 3.4% commission is earned
    12-13% discount then a 3.6% commission is earned
    11-0% discount then a 4% commission is earned

    In the main table I list each sale and the discount given. I'd like to automatically grab the sales amount, factor the appropriate commission level and calculate the commission earned.

    Here's an example, I have a $1,000 sale with a 15% discount, sale amount is $850 with a 3.4% commission earned. The formula would then return a commission of $28.90

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to calculate sales commissions based on % discounted

    Row\Col
    A
    B
    C
    D
    1
    Sale
    Discount
    Comm
    2
    $ 1,000.00
    0%
    $ 40.00
    C2: =A2*(1-B2)*LOOKUP(B2, {0,12,14,16,18}%, {4,3.6,3.4,3.2,3}%)
    3
    $ 1,000.00
    1%
    $ 39.60
    4
    $ 1,000.00
    2%
    $ 39.20
    5
    $ 1,000.00
    3%
    $ 38.80
    6
    $ 1,000.00
    4%
    $ 38.40
    7
    $ 1,000.00
    5%
    $ 38.00
    8
    $ 1,000.00
    6%
    $ 37.60
    9
    $ 1,000.00
    7%
    $ 37.20
    10
    $ 1,000.00
    8%
    $ 36.80
    11
    $ 1,000.00
    9%
    $ 36.40
    12
    $ 1,000.00
    10%
    $ 36.00
    13
    $ 1,000.00
    11%
    $ 35.60
    14
    $ 1,000.00
    12%
    $ 31.68
    15
    $ 1,000.00
    13%
    $ 31.32
    16
    $ 1,000.00
    14%
    $ 29.24
    17
    $ 1,000.00
    15%
    $ 28.90
    18
    $ 1,000.00
    16%
    $ 26.88
    19
    $ 1,000.00
    17%
    $ 26.56
    20
    $ 1,000.00
    18%
    $ 24.60
    21
    $ 1,000.00
    19%
    $ 24.30
    22
    $ 1,000.00
    20%
    $ 24.00
    23
    $ 1,000.00
    21%
    $ 23.70
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    01-05-2015
    Location
    Michigan
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Formula to calculate sales commissions based on % discounted

    Thanks! That worked perfectly, appreciate the quick help!!

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to calculate sales commissions based on % discounted

    You're welcome.

+ 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. Sales Commissions Accumulative formula issues
    By Naf59 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-17-2013, 02:42 AM
  2. [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
  3. [SOLVED] Commission Structure Based on multiple sales packages and commissions
    By arkadd61 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-06-2013, 02:25 AM
  4. Replies: 2
    Last Post: 01-13-2012, 12:05 PM
  5. Calculating Commissions Based on Sales Rank
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-29-2005, 10:56 AM

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