FormulaLogic
Test whether a value falls between two numbers
Check whether a value sits inside a lower and upper bound and return one result if it does, another if it doesn't, with AND nested inside IF.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
=IF(AND(B2>=50, B2<=100), "In range", "Out of range")
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →AND only returns TRUE when both comparisons hold, which is exactly what "between two bounds" means — a value has to clear the lower bound and stay under the upper bound at the same time, not either one alone.
- →Using >= and <= makes both endpoints inclusive; switching either one to a strict > or < excludes that edge from counting as in range.
- →The same shape extends past two conditions — stack additional comparisons inside the same AND to test a value against three or more bounds at once.
The fine print
- →A blank cell used as value compares as 0 in Excel's numeric comparisons, which can quietly count as in range against a lower_bound below zero — worth checking for blanks separately if that matters.
- →lower_bound and upper_bound need to be actual numbers, or numeric cell references, not text that merely looks like a number, or the comparison fails without an obvious error.
- →Works identically in Excel and Google Sheets.