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

Report •


✔ Best Answer
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

http://www.skeptic.com/



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

MIKE

http://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",""))))))

MIKE

http://www.skeptic.com/


Report •

Related Solutions

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

http://www.skeptic.com/


Report •

#5
February 23, 2013 at 12:10:30
Hi Mike,

Went for the lookup option works fine thanks.

Tom


Report •


Ask Question