## Summary

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))
``````

## Generic formula

``=AND(val>MIN(num1,num2),val<MAX(num1,num2))``

## 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)
`````` Author ### 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.