Okay, well as I indicated, there are 4 conditions that could be classed as overlapping (and two that do not). Although we could create a formula to test those 4 conditions with multiple IFs, it is easier to use the other two conditions and if either of them are true (i.e. start point is greater than literature end point OR end point is less than literature start point) then it does not overlap.
In addition to that, we need to test each of your results against every one of the literature values, and to do that we can use an array* formula to cycle through the literature values.
In the attached file I have removed all the spaces in the literature values so that we just have pure numbers, and I have also sorted the values so that it will be easier to locate where any overlaps occur if you need to. I have then put this array* formula in C2:
and copied it down. What this does is to cycle through all the cells in column F to see if A2 (the start point) is above them, and at the same time it cycles through each cell in column E to see if the end point is below them - if either of these are TRUE then zero will be added to a cumulative sum, but if neither of them are true then 1 will be added to that sum (actually, it's equivalent to a count if you think about it). Thus it counts the number of overlaps in the literature records.
You can see that there is only one identified for your range on row 6, and I have highlighted for you where that matches with the literature values.
*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>
Hope this helps (do I get a mention in your thesis?)
Pete
Bookmarks