How to make an interval and search for values within that interval?

Hi Everyone,

I'm a student working on my thesis. I'm having some trouble figuring out how to analyze some of my data.
My thesis is about genetic anomalies. The anomalies lie within a interval of basepairs.

So I have a first column with the starting point of the different anomalies listed vertically. START
The second column lists the end points the same way. END
So one row makes up the START and END point of the interval.

How do I get excel to see these two columns an interval?

Then I need to compare my intervals with known abnormal intervals in the literature.
Is there a way to do this?

Can someone please help me figure out a way?
Thank you in advance!

Can you give us some idea of what the entries in the START and END columns look like, and what we are expected to compare them with? Are they sequences of characters, and if so what is the length of the sequence and how do we determine if a value is between one value and another?

Pete

The genes are made up of chromosomes. The numbers indicate the position on the chromosome (the nucleotides).
The first column is the start position of the genetics anomaly i found in my research. The second column is the end position.
Column E is the start point of the literature anomaly en column F the endpoint. (I am aware that many of the literature values are shown multiple times, but this is of importance too for my project so I didn't delete them for the example sheet)

Now I need to compare my results to results already described in the literature to see of any of the findings are exactly the same or overlap in some way.

So every position in between is of importance too.

Is there a way to achieve this?

It would be fine to if i could just make column A and B an interval and search the literature values in that interval...

Thank you for taking the time to help me!Thesis1.xlsx

I see that some of the digits in the literature column are separated by spaces - are these significant, or can I just get rid of them and threat the resulting string as a number?

Your terminology is a bit misleading, but are you saying that the numbers are equivalent to the start and end points of a "distance", so you want to see if the "length" of any of your results overlap in any way with any of the "lengths" of the literature results?

Pete

Dear Pete,

You can get rid of the spaces in the literature column. They are not important.

I'm sorry for the miscommunications. English isn't my first language (as you may have noticed :p). I think what you have interpreted from my attempt at an explanation is correct. But it's not just about the length.

for example if my result was position 13-25 (start-end), I would need to have a positive search for the following

My result 13-25 Literature 11-18: YES Literature 16-23: YES
Literature 7-28: YES Literature 15-35: YES
So I need to have a positive answer when there is even the slightest bit of overlap.

I hope i'm not making you more confused.

Okay, just to be clear then, the attached pic shows some arbitrary length from the literature column in black. The red lines represent four situations for your experimental data which may start before but finish within, or it could start within and finish within, or it could start within and finish outside, or it could start before and finish after - all these 4 situations should be considered as overlaps. There are two other situations where your data starts and finishes before and where your data starts and finishes after, where there is no overlap.

Is this a correct interpretation?

Pete

Yes, that's it!

Thank you so much for taking the time to help!

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:

Formula:

Please Login or Register  to view this content.

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

Dear Pete,

I was wondering how you identified the one to highlight? Did you do it by "hand"? If so, could I - in one way or another- run the formula in the opposite direction so I would get a 1 next to the literature value too?

Thank you so much!

Yes, I highlighted those cells manually - it is relatively easy to find them as they have been sorted in sequence, so you might like to do the same with your experimental data.

I noticed that the first two entries in the literature columns overlap, so to show that the formula is actually counting the number of overlaps I've added some made-up data to cells A7 and B7 - you can see that the formula now shows those two overlaps (I've coloured them green).

I've put this array* formula in G2 of the attached file:

=IF(SUM(IF((E2>$B$2:$B$7)+(F2<$A$2:$A$7),0,1))>0,"Overlap","")

and copied down. This is very similar to the other one, but I have made the necessary adjustments to the cell and range references. I've also put an IF function around it so that it returns the word "Overlap" or a blank, instead of a series of 1s and 0s, so that it identifies the overlaps more clearly.

*Entered using Ctrl-Shift-Enter (CSE) as described before.

Hope this helps.

Pete

Dear Pete,

i'm so grateful for your help!

Thank you very much.

Gaëlle

You're welcome, Gaëlle - glad to be of help, and good luck in compiling the rest of your thesis.

If that takes care of your original question, please select Thread Tools from the menu link above your first post and mark this thread as SOLVED.

As a general point, you can also pass thanks on more directly to any poster that has helped you (not only in this thread) by clicking on the "star" icon in the bottom left corner of any post that you have found to be helpful - this also adds to the Reputation of the poster (the small green bars in the poster's profile).

Pete