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!

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:
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...

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?

Hi,

Thanks for clarifying. That makes things nice and easy.

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:
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:
Hope that helps...



PS. Welcome to the forum.

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