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

B

bdschleich

Board Regular
Joined
Apr 26, 2005
Messages
133
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Code: 
=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"))))
 
Upvote 0
=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
ABC
13.504
212.53
332
43.51
Sheet1
 
Upvote 0
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.
 
Upvote 0
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)
<sup>edit</sup> Didn't see gauntlet's 2<sup>nd</sup> part... yup, VLookUp(___,___,true) would do the trick too. <sub>/edit</sub>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Pulling Data if Greater Than or Less Than
Replies
5
Views
137
sacajamo
S
S
  • Question
Multiple Conditions - Greater than less than
Replies
6
Views
346
Peter_SSs
Peter_SSs
R
  • Question
COUNTIFS greater than value in A1 but equal to or less than value in A2
Replies
2
Views
1K
RodneyC
R
N
  • Question
How To Create a Gauge-Style Doughnut Chart that Includes Negative & Greater than 100% Numbers
Replies
0
Views
634
NTLKnight
N
R
  • Solved
Data Validation
2
Replies
19
Views
1K
dreid1011
dreid1011

Forum statistics

Threads
1,215,024
Messages
6,122,729
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top