Working out amount of data overlaps

[mrsak87]

I'm trying to work out how much data is overlapping in the attached

Essentially I have 3 columns of data; name, start depth and end depth. Some of the names have either an A or a B at the end of them

In these locations I want to work how much of the data has overlapped.

For example if BH01 went from 0.00 to 10.00 and BH01A went from 8.00 to 20.00 then the overlap would be 2.00. However I can't work out in my head how I'd do this using formula!

Please help my small brain

Sean

[Kaper]

Hi,
In D2 array formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

PS. Please do edit title thread (go to your first post and select edit) to comply with Rule 1 of https://www.excelforum.com/forum-rul...rum-rules.html

[avk]

Try in "D2"

Please Login or Register  to view this content.

copy paste down.
Confirmed by press CTRL+SHIFT+ENTER

End = Sheet1!$C$2:$C$21
Start = =Sheet1!$B$2:$B$21

[mrsak87]

Thank you Kaper, works! - and I've edited the title of the thread

[mrsak87]

avk - I just get "TRUE" in three of the cells, I'm interested in your method though, Do I need to tweak it slightly?

[avk]

If data range overlap with criteria name then shown as "True". (match with first 5 charters)
Define start / End range, by name manager.

[Kaper]

As for my formula - it consists of 3 key elements:

The first key element is the length of segments common part.

If we have 2 segments: a b and c d then common part (provided a<b and c<d can be calculated as

=MIN(b, d)- MAX(a, c)

if this value is positive - it's common part, if is negative is a distance between end of one segment and start of next one.
as we are interested only in common part we can take

=MAX(MIN(b, d)- MAX(a, c), 0)

one of segments is clear our a and b are respectively B2 and C2, but as for second segment (this is second key element of the formula) we shall take leftmost start of any previous segment belonging to the same type/name

so c is MIN(IF(G$1:G1=G2,B$1:B1,""))
and the end of second segment (d) is rightmost part of any previous segment belonging to the same type/name MAX(IF(G$1:G1=G2,C$1:C1,""))

the above 2 will work only if we force excel to do array calculations - first constructing array if IF(G$1:G1=G2,B$1:B1,"") and then finding it's smallest element (and similarly with column C, but largest one)

and the final part (3rd key element) - if it is first occurence of given name/type then there is no segments above to be checked, so we cannot have any overlapping

IF(COUNTIF(G$1:G1,G2)=0,0,

And that is how the formula was constructed

[mrsak87]

Kaper,

Thanks for taking the time to explain to that me! I appreciate that for future references