+ Reply to Thread
Results 1 to 13 of 13

IF Statement Between a Value Range

  1. #1
    Registered User
    Join Date
    01-13-2007
    Posts
    4

    IF Statement Between a Value Range

    Hi,

    This is hard for me to articulate, so hopefully someone will understand what I'm trying to do.

    I'm attempting to construct a formula in Excel, that basically says: IF the value is between these two numbers, multiple them by this percentage, or if the value is between these numbers, multiple it by this percentage.

    So, let's say cell A3 equals 10,000,000, A2 equals 5,000,000 and A1 equals 1,000,000... Also cell B3 equals 10% and B2 equals 5%. We'll say that number X is in cell C1. If the number X is between 10,000,000 & 5,000,000 it should by multiplied by 10%. If number X is between 5,000,000 and 1,000,000 it should be multiplied by 5%.

    I've tried a formula similar to: IF(C1 (Rng(A3:A2), C1*B3) ---- Meaning that if the number is between 10,000,000 and 5,000,000, multiple the number by 10%.

    Anyway, it doesn't seem to work and I think I'm overcomplicating this!

    HELP!

    Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    So, let's say cell A3 equals 10,000,000, A2 equals 5,000,000 and A1 equals 1,000,000... Also cell B3 equals 10% and B2 equals 5%. We'll say that number X is in cell C1. If the number X is between 10,000,000 & 5,000,000 it should by multiplied by 10%. If number X is between 5,000,000 and 1,000,000 it should be multiplied by 5%.

    =if(and(C1<A3,C1>A2),C1*.1,if(and(C1<A2,C1>A1),C1*.05,what do you want to do if it meets neither condition?))

    Also, I purposely interpeted your between 10,000 and 5,000 as not including either number. You may want to chnage < to <= or > to >=.
    not a professional, just trying to assist.....

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    You could try this

    =IF(C1>=A3,C1*B3,IF(C1>=A2,C1*B3,IF(C1>=A1,C1*B2,IF(C1<A1,C1*0))))
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325
    This problem has been solved a zillion of times on the Net...

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote Originally Posted by arthurbr
    This problem has been solved a zillion of times on the Net...
    perhaps you'd like to provide a link to a relevant example?

  6. #6
    Registered User
    Join Date
    01-13-2007
    Posts
    4

    Thanks... How about this?

    Arthur,

    I've scoured the net for this. If you could provide a link, I'd appreciate it.

    Thanks Duane & Chippy....

    How would this work... If B8 is less than A2, it should be multiplied by C2. If B8 is between A3 & A2, it should be multiplied by C3. If B8 is between A4 & A3, it should be multiplied by C4. If B8 is between A5 & A4, it should be multiplied by C5. If B8 is between A5 & A6, it should be multiplied by C6. If B8 is greater than A6, it should be multiplied by C6.

    These are the cell values:

    A2 = 500
    A3 = 1000
    A4 = 1500
    A5 = 2500
    A6 = 3000

    C2 = .20
    C3 = .25
    C4 = .30
    C5 = .35
    C6 = .40

    B8 = VALUE X

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Put a zero in A1 and shift your values from C2:C6 to C1:C5 and then use

    =LOOKUP(B8,A1:C5)*B8

  8. #8
    Registered User
    Join Date
    01-13-2007
    Posts
    4
    That didn't seem to work.

    Anyone else have any ideas?

  9. #9
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Hi smperkin,

    Is this any good?

    arthurbr - This problem has been solved a zillion of times on the Net...

    Make a note of the link!

  10. #10
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote Originally Posted by smperkin
    That didn't seem to work.

    Anyone else have any ideas?
    oldchippy has provided a very good example of how this would work. If you still can't make it happen then please give details of why it "didn't seem to work". Wrong result, no result, error value etc.

  11. #11
    Registered User
    Join Date
    01-11-2007
    Posts
    14
    Got it! Thank you. I am obviously not excel savvy but oldchippy's example helped me figure it out.

  12. #12
    Registered User
    Join Date
    01-13-2007
    Posts
    4
    GOT IT!!!

    Thanks everyone.

    I really appreciate it.

  13. #13
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Glad to help smperkin and niceguyty - thanks for feedback

+ 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