Check if cell value is between cell values in column A and B for multple rows

I'm making an Excel for calculating the variation in perimeter length for expanding and intersecting circles. I have written the file quite far, but have stumbled upon a problematic problem.

I need to determine if sectors of a circle is intersected by other sectors.
Example with 4 holes.gif

The Excel programming problem I have now is to determine if a angle value in column A, row 3 is between any of the sectors denoted by the angle values in row 4-6 (in example file).

Is there a way to do this effeciently, my only idea at the moment is to use e.g.

Please Login or Register  to view this content.

But this is no viable to do for a large number of rows (I need atleast 19).
Also there is another problem with how the sectors are described with angle from -pi (-180 degrees) to pi (+180 degrees). Using the median formula won't work when the start angle and end angle passes from the plus side to the minus side, which is the case for row 6, in the example file.

The example file is for calculations for the yellow circle in the example image.
Example circle sectors.xlsx
Hope you can understand my problem and perhaps have some input or solution.

Thanks for your quick reply!

I hope this quick paint image will make it more understandable: Example with 4 holes - details.gif
Edit: As a clarification, this sheet is only related to the yellow circle, all calculations will then be copied for the other circles.

The reference point is the "top" point of the circle. Start and stop angle represents where the sector starts and ends, in a clockwise fashion.

Regards

Hope I don't misunderstand your suggestion: But it is not whether or not the circles are overlapping which I'm interested in (this I have tested for in another place in the document. But what I finally want to calculate is how the (non-overlapped) perimeter of the circles is varying when the circles grow in size. That why I'm interested in the sectors and whether they are overlapping or not.

I'm going to produce some more illustrative images when I get back home.

Thank you for your patience...

I totally agree that some calculus would have been a nice option, unfortunately I have not managed to find any correlation for this type of problem. Intersection of two circles is easy but when they start interacting with more than one... If you have some calculus realtions in mind, your are very welcome with input. I have posted this question on some math forums without any luck.

A new example file, with more circles added and also expected results in yellow marked cells.
Example2.xlsx
This time I have included all calculations, perhaps you get an better idea of the problem or perhaps see a better solution.

Regards

In this case I have made a figure with all these circles, and looked at it to see if it is overlappen or not. I have done this in wolframalpha by this:
http://www.wolframalpha.com/input/?i...29%5E2%3D3%5E2

So it is matter of checking between the numbers in the columns start angle and end angle, for each row with a "Yes" in Column C "Overlap?"
The tricky part is finding a smart way of doing this check and also taking into account sectors which go by the "pi" to "-pi" leap in the bottom of the circle so to say.

OK, I will try, but am not making any promises.
The goal of the entire project is to calculate the summed perimeter of circles over time as the circles grow at a set speed of e.g. 1 mm/s.


Note that the included sheet is only working with what I have called Circle/Hole 3, the calculations for the other 18 would follow the same pattern.
In Column C I check with some expressions if the two circles (circle no 3 and the respective circle for that row). Expressions and details found e.g. here: http://www.ambrsoft.com/TrigoCalc/Circles2/Circle2.htm

All the way through to column K these calculations are done for each sector separately. But then realising I can not just add up all those values in column K to get the total lost perimeter for circle no 3, since some of the sectors are overlapping/intersecting.

This is where the current question/problem comes in. My approach is then to determine the angles for each sector, (where it starts and where it ends). This can be done either with radians or degrees and also between different intervals. I have chosen radians and [-pi;pi] and to have 0 at the top of the circle.

Since there is a limitation that each sector cannot be more than pi in length (this is a mathematical limitation since the circles are growing at the same rate) I can arrange the angles for each sector in a clockwise order, this is done in column T and U.

Now comes the tricky part where I have not found any feasible solution. The goal is to determine if a start or end angle is "overlapped/intersected" by another sector. My plan was to use the mentioned MEDIAN formula and then somehow iterate down the rows. Also the problem is the sectors which starts with a postive angle and ends with a negative. These won't be correctly evaluated using the median formula.

Hope this clarifies things..despite it being a lot of text.

Thank you very much for your patience and willingness to help.

Ah, ok.

Well this time I made it "by hand". But you could do it by building upon the initial expression:
In cell V5:

Please Login or Register  to view this content.

The problem is that this won't work for row number 5 and 12 in this case. Since their sector is crossing the pi to -pi leap. That is why I'm asking if there is a better solution for this

The pi to -pi leap is the entire reason for this.
Now it'll be math again =)

The circle is divided into radians from -pi to pi (you could have done it with 0 to 360 degrees aswell and end up with the same problem).
I will continue to discuss this in degress instead since that might be easier to follow: If a circle is divided in degrees from 0 to 360, then a point a little bit to the left of the top of the circle would have e.g. 355 degrees, relative the top and a point a little bit to the right would have 5 degrees. Drawing a line between them to form a sector would be 10 degrees long. 355 356 357 358 359 360/0 1 2 3 4 5. In this case the leap would be from 360 to 0 instead. And in this case you would need to divide the "IF MEDIAN" code into two parts, one to check if the value is between 355 and 360 and one to check if the value is between 0 and 5. And this is not the same as checking if it is the median of 355 and 5.

So then in the current case u would need to check these (which go from a positive number to a negative) with two different MEDIAN checks, from e.g. 2.501070751 to PI() and one from -PI() to -.1838878598.

And as I see it, this would mean lots of code, and I have no possibility of checking every single cell if it is correct.

Originally Posted by tunestal

Since there is a limitation that each sector cannot be more than pi in length (this is a mathematical limitation since the circles are growing at the same rate) I can arrange the angles for each sector in a clockwise order, this is done in column T and U.

Yes as stated above.

I have now resolved to programming it in VBA instead, although making the file a bit less user friendly. The code to solve my problem is:

Please Login or Register  to view this content.

Sry for the swedish notes and comments in the code.

The code could probably be more efficient, but this solves the problem atleast.