How to Use the IF Function to Express “Greater Than or Equal To” and “Less Than” Conditions
This article explains how to use the IF function in Excel to specify a range of conditions, such as “greater than or equal to 20 and less than 30,” in a single cell. With this method, you can handle multiple conditions in one formula.
Combining Multiple Conditions
To specify a range condition such as “greater than or equal to” and “less than” in Excel, you combine conditions using the AND or OR functions. Here’s how to handle it with the IF function:
Example: Handling Values Between 20 and 30
For example, to check if the value in cell A1 falls within the range “20 to 30 (exclusive),” and display “Within Range” or “Out of Range” accordingly, use the following formula:
| A | B | |
|---|---|---|
| 1 | 15 | =IF(AND(A1>=20, A1<30), “Within Range”, “Out of Range”) |
| 2 | 25 | =IF(AND(A2>=20, A2<30), “Within Range”, “Out of Range”) |
| 3 | 35 | =IF(AND(A3>=20, A3<30), “Within Range”, “Out of Range”) |
Steps
- Enter the following formula in cell B1: =IF(AND(A1>=20, A1<30), “Within Range”, “Out of Range”)
- If the condition “A1 is greater than or equal to 20 and less than 30” is true, “Within Range” will be displayed.
- If the condition is false, “Out of Range” will be displayed.
Result
If the value in cell A1 is “15,” it will display “Out of Range.” If the value in A2 is “25,” it will display “Within Range,” and if the value in A3 is “35,” it will display “Out of Range.”
Combining Additional Conditions
Similarly, you can use IF in combination with AND or OR functions to evaluate more complex conditions. For example, you can check if the value is “20 to 30 (exclusive)” or “greater than or equal to 50.”
| A | B | |
|---|---|---|
| 1 | 15 | =IF(OR(AND(A1>=20, A1<30), A1>=50), “Condition Met”, “Condition Not Met”) |
| 2 | 25 | =IF(OR(AND(A2>=20, A2<30), A2>=50), “Condition Met”, “Condition Not Met”) |
| 3 | 50 | =IF(OR(AND(A3>=20, A3<30), A3>=50), “Condition Met”, “Condition Not Met”) |
Steps
- Enter the following formula in cell B1: =IF(OR(AND(A1>=20, A1<30), A1>=50), “Condition Met”, “Condition Not Met”)
- This formula checks if A1 is between 20 and 30 (exclusive) or greater than or equal to 50. If either condition is true, it displays “Condition Met.”
- If neither condition is true, it displays “Condition Not Met.”
Result
If the value in cell A1 is “15,” it will display “Condition Not Met.” If the value in A2 is “25,” it will display “Condition Met,” and if the value in A3 is “50,” it will also display “Condition Met.”