+ Reply to Thread
Results 1 to 8 of 8

IF Function with monetary ranges - Commissions

  1. #1
    Registered User
    Join Date
    12-04-2018
    Location
    La Crosse, Wisconsin
    MS-Off Ver
    MICROSOFT EXCEL FOR OFFICE 365 MSO 32-BIT
    Posts
    3

    IF Function with monetary ranges - Commissions

    I will use "C2" as the cell that is always used in this question

    if c2 = $50,000-$54,999, then $200
    if c2 = $55,000-$59,999, then $300
    if c2 = $60,000-$64,999, then $400

    Basically base is $50,000 = $200
    for every additional $5000 you get an additional $100

    Thanks for your help!

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: IF Function with monetary ranges - Commissions

    how about

    Excel 2013 32 bit
    C
    D
    E
    2
    49000
    200
    =MAX(200+INT((C2-50000)/5000)*100,200)
    3
    51000
    200
    4
    59999
    300
    5
    60000
    400
    6
    63000
    400
    7
    70000
    600
    Sheet: Orders

  3. #3
    Banned User!
    Join Date
    10-12-2018
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    358

    Re: IF Function with monetary ranges - Commissions

    I tried to post a formula but it said HTML code not allowed.

  4. #4
    Registered User
    Join Date
    12-04-2018
    Location
    La Crosse, Wisconsin
    MS-Off Ver
    MICROSOFT EXCEL FOR OFFICE 365 MSO 32-BIT
    Posts
    3

    Re: IF Function with monetary ranges - Commissions

    That did not work for me.
    Here is a diagram of what I mean

    sales commission if sales were $69,805, then the commission would be $500.00
    $50,000 $200.00
    $55,000 $300.00
    $60,000 $400.00
    $65,000 $500.00
    $70,000 $600.00
    $75,000 $700.00

    So my idea if a cell is equal to $69,805, then the equation will auto populate $500.00
    Attached Images Attached Images

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: IF Function with monetary ranges - Commissions

    That's exactly what that formula does

    Excel 2013 32 bit
    C
    D
    2
    69805
    500
    3
    119689
    1500
    4
    50000
    200
    5
    55000
    300
    6
    60000
    400
    7
    65000
    500
    8
    70000
    600
    9
    75000
    700
    10
    80000
    800
    11
    85000
    900
    12
    90000
    1000
    13
    95000
    1100
    14
    100000
    1200
    15
    105000
    1300
    16
    110000
    1400
    17
    115000
    1500
    18
    120000
    1600
    19
    125000
    1700
    20
    130000
    1800
    21
    135000
    1900
    22
    140000
    2000
    Sheet: Orders

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: IF Function with monetary ranges - Commissions

    what Fluff gave you should work fine, worked for me. Alternative is to set up a table (like you have in post #4) and use a vlookup like...
    =VLOOKUP(D2,$A$2:$B$21,2,TRUE)
    the "TRUE" at the end will return the correct value where the number falls. Assumptions made were that your sales value is in D2, your table shown in post 4 is in A2 through at least B21 (adjust as needed) and the rest will work.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  7. #7
    Registered User
    Join Date
    12-04-2018
    Location
    La Crosse, Wisconsin
    MS-Off Ver
    MICROSOFT EXCEL FOR OFFICE 365 MSO 32-BIT
    Posts
    3

    Re: IF Function with monetary ranges - Commissions

    OMG guys....it works! I don't know what I did last time but it is AWESOME!!! Thank you guys Fluff13...you are great

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: IF Function with monetary ranges - Commissions

    Glad we could help & thanks for the feedback

+ 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. Replies: 5
    Last Post: 11-11-2015, 03:52 PM
  2. [SOLVED]Function to add total monetary values within a row.
    By communistflamingo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-31-2014, 03:20 PM
  3. function/ formula to track my commissions at a new job
    By excelreject84 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2013, 09:42 AM
  4. Using IF function to calculate tax commissions
    By miremba in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2012, 11:52 AM
  5. How to Assign Monetary Value to Percentages?
    By MissJones in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 01-06-2010, 03:11 PM
  6. Adding a monetary amount on a certain day every month
    By HOOPA74 in forum Excel General
    Replies: 6
    Last Post: 07-07-2009, 03:49 PM

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