Evaluate value between numbers (Excel)

Send Us a Sign! (Contact Us!)

Generic formula

=AND(val>MIN(num1,num2),val<max (num1,num2))

Explanation

To test if a numeric value falls between two numbers, you can use the AND function with two logical tests.

In the example shown, the formula in E5 is:

=AND(D5>MIN(B5,C5),D5<max (B5,C5))

How this formula works

At the core, this formula runs two tests on a value like this:

=D5>MIN(B5,C5) // is D5 > smaller number?
=D5<max (B5,C5) // is D5 < larger number?

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:

=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:

=AND(value>num1,value<num2)