In the first expression, the value is compared to the smaller of the two numbers, determined by the MIN function. In the second expression, the value is compared to the larger of the two numbers, determined by the MAX function. The AND function will return TRUE only when the value is greater than the smaller number and less than the larger number.
Include boundaries
To include the boundary numbers (num1 and num2), just adjust the logic like so: Now the AND function will return TRUE only when the value is greater than or equal to the smaller number and less than or equal to the larger number.
Simple version
The formula in the example is more complex because there is no assumption that num1 is less than num2 (or vice versa). If it’s safe to assume that num1 is less than num2, the formula can be simplified:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
