if / then statement with range of greater than or equal to

[bdschleich]

What is the proper way to construct a formula for an if then statement in order to solve for a value (i.e. "x") that is in cell A1 meet the following criteria:

If X is greater than or equalt to 3.50 . . . then the answer is 1
If X is greater than or equal to 3.00 and less than 3.50 . . . then the answer is 2
If X is greater than or equal to 2.50 and less than 3.00 . . . then the answer is 3
If X is greater than or equal to 0.00 and less than 2.50 . . . then the answer is 4

Thanks!

 

[jproffer]

=IF(A1>=3.5,1,IF(AND(A1>=3,A1<3.5),2,IF(AND(A1>=2.5,A1<3),3,IF(AND(A1>0,A1<2.5),4,"answer not in range"))))

 

[gauntletxg]

=IF(A1>=3.5,1,IF(AND(A1>=3,A1<3.5),2,IF(AND(A1>=2.5,A1<3),3,IF(AND(A1>=0,A1<2.5),4,"Not found."))))

Or with a lookup table:

Excel Workbook
	A	B	C
1	3.5	0	4
2	1	2.5	3
3		3	2
4		3.5	1
Sheet1

 

[neeedhelp]

I'm not sure I understand your question but I think you can use a nested IF function.

=IF(A1>=3.5,1,IF(A1>=3,2,IF(A1>=2.5,3,IF(A1>=0,4,""))))

If the contents of A1 are less than zero this will leave the cell you enter it into blank.

 

[Greg Truby]

Actually, I've not seen any reason to use a nested lookup at all. A simple application of MATCH(___,___,-1) should do the trick:

• =MATCH(A1+0.000000001,{99,3.5,3,2.5},-1)

^edit Didn't see gauntlet's 2^nd part... yup, VLookUp(___,___,true) would do the trick too. _/edit