I have a range of numbers from 1 to 300 and are looking for something that I can use to put then into categories.

In column A I have the list of numbers and as I said they go from 1 to 300, they are all in the cells by themselves and also are repeated throughout the column. In column B, I want to put a formula that will look at the opposite cell in column A and return the various values of 1-30 or 31-60 or 61-90 or 91-150 or 151 -300 or 300-over depending where the value in column A falls, if we cannot use the 1-30 or 31-60 value in column B then I will take anything that I can show

Donâ€™t know if this can be done I also know that I may have to some other /work/ formula to do other things to get it to this result

✔ Best Answer

There is also a =LOOKUP() solution: But you will first need to create a lookup chart like below.

Put it somewhere out of the way like columns Y & Z:Y Z 1) 0 -30 2) 30 30-61 3) 61 61-90 4) 91 91-150 5) 151 151-300 6) 301 300+Then in cell B1 enter the formula:

=VLOOKUP(A1,$Y$1:$Z$6,2,1)

Drag down as many rows as necessary.

A lot shorter then the =IF() function.

MIKE

Why can't you just Sort column A? Then separating them into groups of 30 should only take a few seconds by hand.

MIKE

Hi Mike, I sould have said that this part of other formulas and macros to make the final sheet fully automated so by doing a manual filter it is going against what I am trying to achieve.

Tom

OK, Try this in column B, it is a very long IF statment and uses the ranges you specified in your last post:

=IF(A1>300,"300+",IF(AND(A1<=300,A1>=151),"151_300",IF(AND(A1<=150,A1>=91),"91-150",IF(AND(A1<91,A1>=61),"61-90",IF(AND(A1<61,A1>=30),"30-61",IF(AND(A1>=1,A1<30),"-30",""))))))

MIKE

There is also a =LOOKUP() solution: But you will first need to create a lookup chart like below.

Put it somewhere out of the way like columns Y & Z:Y Z 1) 0 -30 2) 30 30-61 3) 61 61-90 4) 91 91-150 5) 151 151-300 6) 301 300+Then in cell B1 enter the formula:

=VLOOKUP(A1,$Y$1:$Z$6,2,1)

Drag down as many rows as necessary.

A lot shorter then the =IF() function.

MIKE

Hi Mike, Went for the lookup option works fine thanks.

Tom

Ask Your Question

Weekly Poll