+ Reply to Thread
Results 1 to 5 of 5

Need help with creating conditional formula with percentage

  1. #1
    Registered User
    Join Date
    08-03-2013
    Location
    vradyn3852
    MS-Off Ver
    Excel 2003
    Posts
    3

    Smile Need help with creating conditional formula with percentage

    Hi,

    I am new to the forum and have only basic knowledge of Excel. I am trying to figure out how to create a conditional formula, and would be very grateful for your help. Basically, I am trying to calculate a commission on a sales price where the commission is 3% of the sales price if the sales sum is 1.100.000 or less, and 10% commission on any amount exceeding 1.100.000. So, I'm looking for something like:
    If < than 1.100.000, then I want it the cell to return the value of 3% of 1.100.000. If > 1.100.000, then I want the cell to return 3% of 1.100.000 PLUS 10% of any amount that exceeds 1.100.000. Does that make sense?

    Any help appreciated!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Need help with creating conditional formula with percentage

    You can use VLookup for that.

    an excel file, without confidential information will also help to get an better result.
    Last edited by oeldere; 08-03-2013 at 04:50 AM.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need help with creating conditional formula with percentage

    Try this:

    =(A1*3%)+(MAX(0, A1-1100000)*7%)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    08-03-2013
    Location
    vradyn3852
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Need help with creating conditional formula with percentage

    Thanks, I looked up VLookup but I still cant figure out how to do it. I set cell C28 to 1040000 and then tested this formula: =IF(C28>1100000,3%, IF(C28>1100000,10%)), but then I got the output FALSE. I don´t get it, according to my logic the output should then be 3% of 1040000?

    And in any case the formula would still not work for me because if it is over 1100000 it should not be 10% of the whole sum, but of the amount exceeding 1100000.

    Let me underscore that I am extremely amateurish at Excel, so I need to have it explained like a 5-year old....

    Anyone?

  5. #5
    Registered User
    Join Date
    08-03-2013
    Location
    vradyn3852
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Need help with creating conditional formula with percentage

    Perfect! That worked! Thank you so much!

+ 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. [SOLVED] Need help creating an IF formula to add a percentage based on multiple criteria
    By jbear536 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-23-2013, 11:38 AM
  2. Replies: 3
    Last Post: 01-21-2013, 09:07 PM
  3. Replies: 0
    Last Post: 10-11-2012, 11:38 AM
  4. Help creating conditional formula
    By neilpateluk in forum Excel General
    Replies: 1
    Last Post: 01-15-2009, 12:32 PM
  5. Need Help Creating a Conditional Formula
    By judym in forum Excel General
    Replies: 4
    Last Post: 07-11-2008, 04:43 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