Table of Contents
The Excel formula ‘If Greater Than But Less Than’ is an incredibly useful tool that can be applied to a wide range of situations. This formula is commonly used to create conditional statements in Excel that can help identify and analyze data more efficiently. This formula is especially useful in situations where multiple criteria must be met in order to return a desired result. It can be used to compare two numerical values, such as sales figures from two different months, or to compare two dates, such as the expiration date of a product. With this formula, you can quickly and easily determine if a value is, for example, between two specified values or greater than or less than a specific value. This formula can be used to create a number of different scenarios and is an excellent tool to have in your Excel arsenal.
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:
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.