[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Excel Formula Question

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
taiell0
Asked:
taiell0
1 Solution
 
RunriggerCommented:
TRY THIS

=IF(A1<12,-2,IF(A1<24,-1,IF(A1<36,0,IF(A1<48,1,2))))
0
 
jppintoCommented:
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
 
jppintoCommented:
Example attached...
Book1.xlsx
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
RunriggerCommented:
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
 
taiell0Author Commented:
That was bad ass. Thank you!
0
 
barry houdiniCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now