Excel Formula to Test Whether Two Ranges Overlap
Question details
The user needs an Excel formula to check if two numerical ranges overlap, including scenarios where one contains the other or endpoints touch.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two sets of numerical boundaries (lower and upper bounds) to identify intersections.
- Observed behavior
- The goal is to return a TRUE/FALSE value indicating overlap, or to calculate the exact numerical amount of the overlap.
Ensure that your lower and upper bound values are placed in separate cells for both ranges, and that the lower bound is always less than the upper bound.
Use the AND Function to Check for Overlap
This is the most efficient and recommended way to return a TRUE or FALSE result when checking if two ranges overlap.
This formula checks if the start of the second range is less than or equal to the end of the first range, and if the end of the second range is greater than or equal to the start of the first range.
Place the lower and upper bounds of Range 1 in cells A2 and B2 respectively. Place the lower and upper bounds of Range 2 in cells C2 and D2.
Select an empty cell where you want the result to appear and type the formula: =AND(C2<=B2,D2>=A2)
Press Enter. The cell will display TRUE if the ranges overlap or their endpoints touch, and FALSE if they are completely separate.
Calculate the Exact Amount of Overlap
Use the MIN and MAX functions to determine the numerical size of the intersection between two ranges.
Easily Manage Formulas with WPS Spreadsheet
WPS Spreadsheet provides robust support for logical and mathematical functions like AND, MIN, and MAX, making it simple to calculate range overlaps and analyze complex data sets with zero hassle.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data ranges.
- 2. Organize your boundaries: Ensure your lower and upper bounds are cleanly separated into distinct columns.
- 3. Apply the formula: Type the overlap formula such as =AND(C2<=B2,D2>=A2) into your target cell.
- 4. Fill down the column: Click and drag the fill handle at the bottom-right of the cell to apply the formula across multiple rows effortlessly.
Frequently Asked Questions
What if the endpoints of my ranges are not in order?
If your lower and upper bounds might be swapped in the columns, you should normalize them within the calculation. You can wrap the cell references in MIN and MAX to guarantee the smaller number is always treated as the lower bound.
How can I return TRUE/FALSE using the overlap amount formula?
You can convert the overlap distance calculation into a logical test by checking if it is greater than zero. Use the formula: =MAX(0, MIN(D2,B2) - MAX(A2,C2)) > 0.
Can I use Conditional Formatting to highlight overlapping ranges?
Yes. Select your data range, go to Conditional Formatting > New Rule, choose 'Use a formula to determine which cells to format', and input the overlapping AND formula (e.g., =AND($C2<=$B2,$D2>=$A2)) to automatically color-code overlapping rows.
