ISBETWEENformula 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
- Examples of using
ISBETWEENformula not working?
- Similar formulas to
ISBETWEEN formula with the syntax shown below, it has 3 required parameters and 2 optional 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).
ExamplesHere are a few example use cases that explain how to use the
ISBETWEENformula 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.
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.
ISBETWEENnot working? Here are some common mistakes people make when using the
ISBETWEENGoogle 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
upper_value_is_inclusive parameters correctly, or the formula may return an incorrect result.
Using non-numeric values for comparison
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:
IFformula 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.
ANDfunction 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.
ORformula 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.
COUNTIFformula 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.
AVERAGEIFfunction 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.
You can learn more about the
ISBETWEEN Google Sheets function on Google Support.