Explanation
At the core, this formula runs two tests on a value like this:
=D5>MIN(B5,C5) // is D5 greater than smaller?
=D5<MAX(B5,C5)) // is D5 less than larger?
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), adjust the logical operators like this:
=AND(D5>=MIN(B5,C5),D5<=MAX(B5,C5))
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 like this:
=AND(value>num1,val<num2)
Related formulas
Value is within tolerance
Count values out of tolerance
Related functions
AND Function
The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1<10). The AND function can be used as the logical test inside the IF...
MAX Function
The Excel MAX function returns the largest numeric value in the data provided. MAX ignores empty cells, the logical values TRUE and FALSE, and text values.
MIN Function
The Excel MIN function returns the smallest numeric value in the data provided. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
