Comparing two ranges of numbers and say if the first overlaps the second

I'm new to Excel and I need to use it for a scientific paper. I'm trying to optimize my database data gathering workflow.

I need to compare the range created by the columns "Start" and "End" to the different ranges inside the yellow square and output, in the "co-localization" column, if the Start-End range overlaps just the first value (Yes - Start), just the second value (Yes - End), overlaps both (Yes - both) or is just contained (Yes - Contained). If none of the conditions is met, just output "No".

Is this too complex for Excel?

Thanks!

btw: I tagged this question Excel 2010, because that's what I use at home, but if you know how to do this in earlier version I would gladly accept that answer too.

-
I would love to help, but I don't really want to put in my email address to look at the file you posted. Did you know that you can post files on Microsoft Web Apps (office.microsoft.com/en-us/web-apps) for no cost? From there you can share them publicly and people don't need to login or provide info to download your file. If you post it up there you may get a few more people (including myself) to help you out. – Ben Jones Mar 4 '11 at 20:40
Done ;) I didn't know about that service. Thanks – Santiago Mar 4 '11 at 20:47
If an answer works for you, you should accept it. See FAQ – chris neilsen Mar 6 '11 at 0:08
I can't accept the answer, it says I need a rep of 15 – Santiago Mar 6 '11 at 11:02
There you go :) – Phoshi Mar 7 '11 at 9:16

Here a solution:

First a small change to your sheet layout (values in range F2:G11)

``````From            To
0
0    27800000
34400000    46500000
51000000   102000000
107000000   117600000
120700000   153300000
154800000   163800000
171200000   174300000
184000000   247200000
999999999   999999999
``````

Add 4intermediate result columns (i've used I, J, K, L)

``````I2    =MATCH(A2,\$F\$2:\$F\$11,1)
J2    =MATCH(A2,\$G\$2:\$G\$11,1)+1
K2    =MATCH(B2,\$F\$2:\$F\$11,1)
L2    =MATCH(B2,\$G\$2:\$G\$11,1)+1
``````

Copy these down for all rows in your data

Formula Co-localization Cell

``````D2   =IF(AND(I2=J2,I2=K2,I2=L2),"Yes - Contained",IF(AND(I2=J2,K2=L2),"Yes - Both",IF(I2=J2,"Yes - Start",IF(K2=L2,"Yes - End","No"))))
``````

To cope with the additional criteria for "Yes - Middle", add another criteria to the formula to test for spanning a from/to range

``````D2 =IF(AND(I2=J2,I2=K2,I2=L2),"Yes - Contained",IF(AND(I2=J2,K2=L2),"Yes - Both",IF(I2=J2,"Yes - Start",IF(K2=L2,"Yes - End",IF(AND(I2+1=J2,K2+1=L2,I2<K2),"Yes - Middle","No")))))
``````
-
Thank you! I think this does it, but could you explain me why are the extra lines "0" and "999..." needed? – Santiago Mar 5 '11 at 21:41
to avoid the `MATCH`'s returning n/a errors for 'To' < 2780000 and incorrect results From > 18400000 or To > 247200000 (i should have mentioned, the extra last value needs to be > any possible From/To value) – chris neilsen Mar 5 '11 at 22:05
@chris neilsen: Thanks for the explanation. But isn't the 999.. cell useless since your formula doesn't select it? (still works flawlessly though) – Santiago Mar 6 '11 at 9:52
btw: it's funny.. the formulas won't work in google docs (they output n/a) but work everywhere else. Just a side note, no big deal. – Santiago Mar 6 '11 at 10:50
@chris neilsen: I found a situation where your formula isn't working: the case when a sequence begins and ends outside the F2:G11 data set, but crosses it (begin>...F2:G11 data..>end). As of now this results in a "No" answer, when maybe "Yes - middle" was more appropriate. I didn't ask for this in my original request, this is a question that has risen after having put your great formula to work. How could I do this? – Santiago Mar 6 '11 at 22:42