ISBETWEEN

The ISBETWEEN formula is an operator that checks if a given value is between two other values. It returns a boolean value of TRUE if the value to compare is between the lower and upper values (inclusive or exclusive based on the optional parameters). Otherwise, it returns FALSE. This formula is commonly used in conditional formatting rules or data validation rules.

How to use ISBETWEEN formula?
Examples of using ISBETWEEN formula
ISBETWEEN formula not working?
Similar formulas to ISBETWEEN

Usage

Use the ISBETWEEN formula with the syntax shown below, it has 3 required parameters and 2 optional parameters:

=ISBETWEEN(value_to_compare, lower_value, upper_value, lower_value_is_inclusive, upper_value_is_inclusive)

Parameters:

value_to_compare (required):
The value that will be compared to the range of values.
lower_value (required):
The lower bound of the range of values to compare against.
upper_value (required):
The upper bound of the range of values to compare against.
lower_value_is_inclusive (optional):
An optional boolean value that determines whether the lower bound is inclusive or exclusive. Default is TRUE (inclusive).
upper_value_is_inclusive (optional):
An optional boolean value that determines whether the upper bound is inclusive or exclusive. Default is TRUE (inclusive).

Examples

Here are a few example use cases that explain how to use the ISBETWEEN formula in Google Sheets.

Highlighting values within a range

You can use ISBETWEEN to highlight values in a range that fall within a specific range of values. For example, you can use this formula to highlight all values in a column that are between 5 and 10.

Data validation

You can use ISBETWEEN as part of a data validation rule to ensure that a cell's value falls within a specific range of values. For example, you can use this formula to validate that a user's input falls within a specific range.

Counting values within a range

You can use ISBETWEEN as part of a COUNTIF formula to count the number of values in a range that fall within a specific range of values. For example, you can use this formula to count the number of values in a column that are between 5 and 10.

Common Mistakes

ISBETWEEN not working? Here are some common mistakes people make when using the ISBETWEEN Google Sheets Formula:

Forgetting to include the value to compare

One of the most common mistakes when using ISBETWEEN formula is to forget to include the value to compare.

Incorrectly setting the bounds as inclusive or exclusive

It's important to set the lower_value_is_inclusive and upper_value_is_inclusive parameters correctly, or the formula may return an incorrect result.

Using non-numeric values for comparison

The ISBETWEEN formula only works with numeric values, so if you attempt to use it with non-numeric values, it will return an error.

Reversing the order of the lower and upper bounds

It's important to specify the lower bound before the upper bound, or the formula may return an incorrect result.

Not accounting for rounding errors

When using the ISBETWEEN formula with floating point numbers, it's important to account for rounding errors in the comparison.

The following functions are similar to ISBETWEEN or are often used with it in a formula:

IF
The IF formula is a logical function used to test a condition and return one value if the condition is true and another value if the condition is false. It is commonly used to create conditional statements and perform calculations based on certain conditions.
AND
The AND function in Google Sheets is a logical function that returns TRUE if all of the logical expressions in the argument are TRUE, and FALSE if any of the logical expressions are FALSE. It can be used to test multiple conditions and evaluate whether they are all true or not.
OR
The OR formula is a logical formula that returns TRUE if at least one of the provided logical expressions is TRUE. It returns FALSE if all logical expressions are FALSE. This formula is commonly used in conjunction with other logical formulas, such as AND, to build more complex logical statements.
COUNTIF
The COUNTIF formula counts the number of cells within a specified range that meet a certain criterion. This formula is commonly used to count cells that meet a specific condition or criteria.
AVERAGEIF
The AVERAGEIF function calculates the average of a range of cells that meet a specified criteria. It is commonly used when working with large datasets to quickly calculate the average of a subset of data. The function takes a range of cells to evaluate (criteria_range), a string or value to compare against (criterion), and an optional range of cells to average (average_range). If the average_range is not specified, the function will use the same range as the criteria_range.

Learn More

You can learn more about the ISBETWEEN Google Sheets function on Google Support.