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

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History