x
Solved

# Excel Formula Question

Posted on 2011-02-23
Medium Priority
247 Views
I've been trying to figure this out on my own for a week. Formulas just aren't my strong suit.

I have a column of data in A. From A1 to A200 let's say. I want a formula that will test each value in column A and assign it a different value in B. For example, if A1 is between 1 and 11, I would like a value in B1 of -1 and I want to carry this on all the way down the line.

The range values I am look at are:
1-11: -2
12-23: -1
24-35: 0
36-47: 1
48-59: 2

Arcane Excel Wizards, I call upon your dark powers to aid me....
0
Question by:taiell0

LVL 11

Expert Comment

ID: 34960494
TRY THIS

=IF(A1<12,-2,IF(A1<24,-1,IF(A1<36,0,IF(A1<48,1,2))))
0

LVL 33

Accepted Solution

jppinto earned 2000 total points
ID: 34960517
Put this in cell B1 and copy down:

=IF(AND(A1>=1,A1<=11),-2,IF(AND(A1>=12,A1<=23),-1,IF(AND(A1>=24,A1<=35),0,IF(AND(A1>=36,A1<=47),1,IF(AND(A1>=48,A1<=59),2,"")))))

jppinto
0

LVL 33

Expert Comment

ID: 34960525
Example attached...
Book1.xlsx
0

LVL 11

Expert Comment

ID: 34960543
jppinto, I don't think that you really need something to test all conditions, the requirement enters a different result for increasing values, so you only need to test the upper range of each in ascending order (or lower range or each in a descending order).

My only omission was to enter a catch all for value greater than 48 of "2" which you have done;

=IF(A1<12,-2,IF(A1<24,-1,IF(A1<36,0,IF(A1<48,1,IF(A1<60,2,"")))))

would give the same result.
0

Author Closing Comment

ID: 34960547
That was bad ass. Thank you!
0

LVL 50

Expert Comment

ID: 34960551
You could use LOOKUP like this in B1 copied down, which can be generalised for any ranges and or values to be returned......

=LOOKUP(A1,{1,12,24,36,48;-2,-1,0,1,2)

....or for your specific values in your example, becauise there's a consistent pattern

=INT(A1/12)-2
regards, barry
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.