How can I write an Excel formula that checks if a value is greater than one number but less than another?

By /

The process of creating an Excel formula that can check if a value falls within a specific range involves using the “IF” and “AND” functions. The “IF” function allows you to specify a condition, while the “AND” function allows you to combine multiple conditions. By using these two functions together, you can create a formula that checks if a value is greater than one number and less than another. This formula can be useful in various scenarios, such as managing budgets, analyzing data, and setting up conditional formatting. With the correct syntax and logical operators, you can easily create a formula that accurately checks if a value falls within a specific range in Excel.

Excel Formula: If Greater Than But Less Than

You can use the following formula in Excel to check if the value in a particular cell is greater than one value but less than another value:

=IF(AND(B2>20,B2<30),"Yes", "No")

This particular formula checks if the value in cell B2 is greater than 20 but less than 30 and returns “Yes” or “No” as a result.

The following example shows how to use this formula in practice.

Example: If Greater Than But Less Than Formula in Excel

Suppose we have the following dataset in Excel that shows the number of points scored by basketball players on various teams:

Suppose we would like to check if each value in the Points column is greater than 20 but less than 30.

We can type the following formula into cell C2 to do so:

=IF(AND(B2>20,B2<30),"Yes", "No")

We can then click and drag this formula down to each row in column C:

Excel if greater than but less than

Column C now returns either “Yes” or “No” to indicate if each corresponding value in column B is greater than 20 but less than 30.

For example:

  • The value 22 is greater than 20 but less than 30 so the formula returns Yes.
  • The value 14 is not greater than 20 but less than 30 so the formula returns No.
  • The value 17 is not greater than 20 but less than 30 so the formula returns No.

And so on.

Note: In this example we chose to return the values “Yes” or “No” but you can replace these values with whatever values you’d like to return.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel Formula: If Greater Than But Less Than
  2. Google Sheets: COUNTIF Greater Than But Less Than Some Number
  3. How to Use Greater Than & Less Than in MongoDB Queries
  4. How do I sum a column if the value in that column is less than a given value?
  5. Can I write a Google Sheets formula to return “Yes” or “No” if there is a match?
  6. Which R function checks if a string contains multiple substrings?
  7. How do I use conditional formatting if a cell is less than or equal to a value?
  8. What does it mean when a p value is less than 0.001?
  9. How can I use conditional formatting in Google Sheets to highlight cells that are greater than or equal to a certain value?
  10. How can I remove a formula in Excel, but keep the resulting value?