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.

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