How do I construct an Excel formula to return a specific value if a cell is between two numbers?

By /

The process of constructing an Excel formula to return a specific value if a cell is between two numbers involves using the IF function along with logical operators such as greater than, less than, and equal to. The formula should first check if the cell value meets the criteria of being between the two numbers, and if it does, it will return the desired value. Otherwise, it will return a blank or an error message. This allows for conditional statements to be applied to the data in the cells, providing efficient and accurate results. By following this process, one can easily manipulate and analyze data in Excel based on specific criteria.

Excel Formula: If Between Two Numbers then Return Value

You can use the following formula to return a value in Excel only if it falls between two specific numbers:

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

This particular formula checks if the value in cell B2 is between 20 and 30.

If the value is between these numbers, then the formula simply returns the value.

Otherwise, the formula returns a blank.

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

Example: Return Value if Between Two Numbers in Excel

Suppose we have the following dataset in Excel that contains information about points scored by various basketball players:

We can use the following formula to check if each value in the Points column is between 20 and 30:

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

We can type this formula into cell C2 and then click and drag it down to each remaining cell in column C:

Excel if between two numbers then return value

If the value in column B is between 20 and 30, then column C simply returns the value.

Otherwise, column C returns a blank.

Note that you could also return other values using the IF function if you’d like.

For example, you could instead use the following formula to return either “Yes” or “No” to indicate if a value falls between 20 and 30:

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

The following screenshot shows how to use this formula in practice:

Column C now returns either “Yes” or “No” to indicate if the corresponding value in column B is between 20 and 30.

Additional Resources

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

x

Related terms:

  1. Excel Formula: If Between Two Numbers then Return Value
  2. Excel Formula: If Blank Then Return Value of Another Cell
  3. How can I create an Excel formula that returns the value of another cell if a certain cell is blank?
  4. How can I create an Excel formula that will only return a value if it is an exact match?
  5. How can I create an Excel formula that performs a specific action if a cell is formatted with the color green?
  6. Excel Formula: Replace If Cell Contains Specific Text
  7. How can I use VLOOKUP to return the value in the next cell in Excel?
  8. Excel: If Cell Contains Text then Return Value
  9. How to Use VLOOKUP to Return Value in Next Cell in Excel?
  10. How do I find the first 3 positive numbers in Excel when I have a column of numbers?