Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    30

    Unanswered: checking whether a value is within a range

    Hello,
    I have a feeling that my problem is really simple, but I can’t figure it out.
    In column A I have a list of values ranging from 1-150
    In column B I want Excel to insert a specific number, if the value in column A falls within a certain range.
    The ranges are:
    1-2
    2-4
    4-8
    8-16
    16-32
    32-64
    64-128

    For example:
    If the value in column A is 1.8, i.e., within the range of the first group, the number in column B should be 1
    If the value in column A is 9, i.e., within the range of 4th group, then the number in column B should be 4
    Etc...

    Thanks a lot for your help!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    There are two things to do.

    1) Don't have the number ranges (such as "1-2") as text in a single cell. Instead, use two columns like this:
    Code:
    A B
    1 2
    2 4
    
    etc...
    2) At the moment there are overlaps in your number ranges. The number 2 falls into category 1 and category 2. Please clarify?


    Hope that helps...

  3. #3
    Join Date
    Mar 2010
    Posts
    30
    Thanks Colin.
    You are right, the correct ranges are:
    1-1.9
    2-3.9
    4-7.9
    8-15.9
    16-31.9
    32-63.9
    64-128

    Do i need to use IF within IF... until i cover all the different groups?

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Thanks for clarifying. That makes things nice and easy.

    Do i need to use IF within IF... until i cover all the different groups?
    You could do that, but there's a better way.

    In fact, in this case you only need 1 column which contains the minima for each number grouping. Suppose, for example, that you have this in cells A1:A7:
    Code:
    1
    2
    4
    8
    16
    32
    64
    Now we can use the MATCH() function. For example, where does 1.2 fall into?
    =MATCH(1.2,A1:A7,1)

    Or, where does 9.3 fall into?
    =MATCH(9.3,A1:A7,1)


    General Notes
    • With this set up, if you try to look up using a number less than 1 (which is the smallest number in the look up table) then the formula will return #N/A. If you want to avoid #N/A errors then the first number in your lookup table must always contain a value lower than anything you would ever lookup from.
    • Any number equal to or greater than 64 (which is the largest number in the look up table) will return 7 (which is the last "group").
    • The lookup table must be sorted smallest to largest (ascending).



    MATCH() Function Notes

    The 1 argument in the third ([match_type]) parameter has the following meaning per the Excel helpfile:
    Quote Originally Posted by Helpfile
    Match_type Behavior
    1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
    Hope that helps...



    PS. Welcome to the forum.

  5. #5
    Join Date
    Mar 2010
    Posts
    30
    It worked!!!
    I've been trying to use IF function for hours with no luck...

    Thanks a lot for your help, and i'm happy that i found this forum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •