# Solved Fixing a Range of Numbers February 23, 2013 at 08:36:05
Specs: Windows 7
 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 See More: Fixing a Range of Numbers February 23, 2013 at 11:06:37
 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

#1 February 23, 2013 at 09:22:19
 Why can't you just Sort column A?Then separating them into groups of 30 should only take a few seconds by hand.MIKEhttp://www.skeptic.com/

Report •

#2
February 23, 2013 at 09:35:50
 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

Report •

#3 February 23, 2013 at 10:24:44
 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",""))))))MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4 February 23, 2013 at 11:06:37
 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 