Intersection of two data ranges.

[bamaboy]

[TABLE="width: 100, align: left"]
<tbody>[TR]
[TD]Elevation A[/TD]
[TD]Elevation B[/TD]
[TD]Elevation C[/TD]
[TD]Elevation D[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]32[/TD]
[TD]31[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]






I have two ranges of elevations (Elevation A and B and Elevation C and D) and I need a formula to determine if the two sets overlap. For example, Elevation A starts at 24 and ends at 32 and Elevation C starts at 31 and ends at 25. For this example, there is an overlap (31 and 32). In the third row of the above table, Elevation A starts at 15 and ends at 20, while Elevation C starts at 25 and ends at 30, so there is no overlap.

I am hoping that someone can help me with a formula to simply return a "yes" or "no" answer depending on whether the data ranges intersect.

Thanks in advance!!

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want. If not, post back with more details/examples.

You didn't clarify what to do in the case of my row 7. If you want this to say "Yes" change < and > to <= and >=

Excel Workbook
	A	B	C	D	E
1	Elevation A	Elevation B	Elevation C	Elevation D	Overlap
2	24	32	31	35	Yes
3	15	20	25	30	No
4	15	20	5	10	No
5	20	40	26	28	Yes
6	24	32	18	25	Yes
7	20	30	30	40	No
Overlap

 

[bamaboy]

Peter,
Thank you so much....that is exactly what I needed!!!

 

[Peter_SSs]

Peter,
Thank you so much....that is exactly what I needed!!!

You're welcome. Glad it worked for you & thanks for letting us know.