+ Reply to Thread
Results 1 to 7 of 7

IF function with multiple possibilities

  1. #1
    Registered User
    Join Date
    01-05-2010
    Location
    San Diego, California
    MS-Off Ver
    Excel 2003
    Posts
    4

    IF function with multiple possibilities

    Hey,

    I want to create a rule or an IF function to give me a percentage based on a range of possible totals. Example:

    If value is under 100, 15% will be charged.
    If value is between 101-200, 10% will be charged.
    If value is 201 or greater, 5% will be charged.

    Say that my values are in column C and I want the corresponding percentage to be generated using a rule or IF function to be displayed in column A. Is this possible? If so, how would write the function?

    (I only need the number to appear) ex: If my value is 120 in column C, I want the function to insert 10% in column A.

    Sorry if this not worded or explained clearly, but thank you for the help.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,298

    Re: IF function with multiple possibilities

    This formula does what you require.

    =IF(C1<=100,C1*0.15,IF(AND(C1>100,C1<=200),C1*0.1,IF(C1>200,C1*0.05,"")))

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: IF function with multiple possibilities

    Alternative approach

    according to this
    If my value is 120 in column C, I want the function to insert 10% in column A.
    If you just want to show the percentage in column A, then use

    =LOOKUP(C1,{0,100,200},{0.15,0.1,0.05})

    format as %

    Or calculate the 10% of the 120 in column C and show the result 12 without an IF statement

    =LOOKUP(C1,{0,100,200},{0.15,0.1,0.05})*C1

    format as number, decimals as required.

  4. #4
    Registered User
    Join Date
    01-05-2010
    Location
    San Diego, California
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: IF function with multiple possibilities

    Thank you teylyn that does exactly what I needed!

    Is it possible to set the function on an entire column? Referencing my first post: if I had for example 50 random totals in column C and I wanted column A to display the percentage; can I make the function give me a percentage for each value I have in Column C?

    The first LOOKUP function is exactly what I want I just don't know how to make it so the function works for the entire column and I don't have to rewrite the function multiple times for each value.

    Thanks again.

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: IF function with multiple possibilities

    Put the function into a cell in row 1 and then just copy it down. If your first row has headers, and you want the formula to start in row 2, adjust it to


    =LOOKUP(C2,{0,100,200},{0.15,0.1,0.05})

    before copying it down.

    hth

  6. #6
    Registered User
    Join Date
    08-15-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    1

    Re: IF function with multiple possibilities

    Hey Guys,
    I have an IF formula that is working, but I want to add to it
    =IF(D6="I","",IF(B6>0,B6-K6,IF(B6<0,B6-L6)))
    I would like to add that if C6="ST" then this column will remain blank as well, not sure why I can't get it to work.
    I have tried
    =IF(D6="I","",IF(B6>0,B6-K6,IF(B6<0,B6-L6,IF(C6="ST",""))))

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,730

    Re: IF function with multiple possibilities

    Rickastar welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    (check to make sure that D6 really does contain I, and that there are no leading/trailing spaces)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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