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.

IF(OR(A3=MEDIAN(A3;A4;B4);A3=MEDIAN(A3;A5;B5);A3=MEDIAN(A3;A6;B6));"Yes";"No")

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.

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.

Ah, ok.

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

=IF(OR(T5=MEDIAN(T5;T6;U6);T5=MEDIAN(T5;T7;U7);T5=MEDIAN(T5;T8;U8)...adding all the rest of the rows);"Yes";"No")

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.

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:

Sub LoopRange_All()

'OL = Outside loop (alltså att stega vilken cell som är referens)
'IL = Inside loop (alltså loopa över alla vinklar, när den kollar)
    Dim rRow_OL As Range
    Dim rRng As Range
    Dim Comp As Integer
    Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
'            '   Exclude certain sheets
            If ws.Name = "Input" Then
                MsgBox "Kommer inte köra " & ws.Name
                Else
'            MsgBox "För detta sheet kommer den köraa nu" & ws.Name
'
            MsgBox "Hej nu är jag på " & ws.Name
'Ange vilket intervall som ska kollas, alltså kolumner och rader för Start vinkel och **** vinkel
    Set rRng = ws.Range("T6:U23")
    
'    Rensa "Is overlapped fälten"
    ws.Range("V6:W23").Clear

'Stega referens cell
    For Each rCol_OL In rRng.Columns
        For Each rRow_OL In rCol_OL.Rows
            Debug.Print rRow_OL.Address, rRow_OL.Value, rRow_OL.Column
            If rRow_OL.Value = "N/A" Then
            Debug.Print "Detta är hålet på sig självt"
            Else
                       
              
'Stega i kontrollintervaller (bara rader i första kolumnen)
'              For Each rCol_IL In rRng.Columns
        For Each rRow_IL In rCol_OL.Rows
            
            
'            Kolla om vänster eller höger kolumn
            If rRow_OL.Column = 20 Then
            Comp = 1
            
            ElseIf rRow_OL.Column = 21 Then
            Comp = -1
            
            End If
            
            
            Debug.Print rRow_IL.Address, rRow_IL.Value, rRow_IL.Offset(0, Comp).Value

'            Kolla om det är siffror i cellen eller ett N/A (eftersom det är hålet själv)
            If rRow_IL.Value = "N/A" Then
            Debug.Print "Det är N/A här"
            Else

            

'Få den att undivka att kolla MEDIAN om den är på sin "egen" rad

            If rRow_OL.Value = rRow_IL.Value Or rRow_IL.Offset(0, Comp).Value = rRow_OL.Value Then
            Debug.Print "Den försöker kolla median mot sig själv.", rRow_IL.Value, rRow_IL.Offset(0, Comp).Value, rRow_OL.Value
            
            Else

            'Kolla om den är i första kolumnen
            If Comp = 1 Then

'            Kolla om specialfall
            If rRow_IL.Value < rRow_IL.Offset(0, Comp).Value Then


'            Om inte specialfall
            If rRow_OL.Value = WorksheetFunction.Median(rRow_IL.Value, rRow_IL.Offset(0, Comp).Value, rRow_OL.Value) Then
            Debug.Print "Ja, den är innanför en annan sektor", rRow_IL.Value, rRow_IL.Offset(0, Comp).Value, rRow_OL.Value
' Nåt smart kommando för att markera vilken cirkel det är som överlappar.
            rRow_OL.Offset(0, 2).Value = rRow_OL.Offset(0, 2).Value & rRow_IL.Offset(0, -19).Value
            Else
            Debug.Print "Nej"
            End If


'            Om specialfall (går över pi till -pi hoppet)
            Else

            Debug.Print test
            If rRow_OL.Value = WorksheetFunction.Median(rRow_IL.Value, rRow_IL.Offset(0, Comp).Value, rRow_OL.Value) Then
            Debug.Print "Nej"
            Else
            Debug.Print "Ja, den är innanför en annan sektor", rRow_IL.Value, rRow_IL.Offset(0, Comp).Value, rRow_OL.Value
 ' Nåt smart kommando för att markera vilken cirkel det är som överlappar.
            rRow_OL.Offset(0, 2).Value = rRow_OL.Offset(0, 2).Value & rRow_IL.Offset(0, -19).Value
            End If

            End If



'            Om den är i andra kolumnen
            ElseIf Comp = -1 Then

'            Kolla om specialfall

            If rRow_IL.Value > rRow_IL.Offset(0, Comp).Value Then
'            Om inte specialfall
            If rRow_OL.Value = WorksheetFunction.Median(rRow_IL.Value, rRow_IL.Offset(0, Comp).Value, rRow_OL.Value) Then
            Debug.Print "Ja, den är innanför en annan sektor", rRow_IL.Value, rRow_IL.Offset(0, Comp).Value, rRow_OL.Value
 ' Nåt smart kommando för att markera vilken cirkel det är som överlappar.
            rRow_OL.Offset(0, 2).Value = rRow_OL.Offset(0, 2).Value & rRow_IL.Offset(0, -20).Value
            Else
            Debug.Print "Nej"
            End If

'            '            Om specialfall (går över pi till -pi hoppet)
            Else


            If rRow_OL.Value = WorksheetFunction.Median(rRow_IL.Value, rRow_IL.Offset(0, Comp).Value, rRow_OL.Value) Then
            Debug.Print "Nej"
            Else
            Debug.Print "Ja, den är innanför en annan sektor", rRow_IL.Value, rRow_IL.Offset(0, Comp).Value, rRow_OL.Value
  ' Nåt smart kommando för att markera vilken cirkel det är som överlappar.
            rRow_OL.Offset(0, 2).Value = rRow_OL.Offset(0, 2).Value & rRow_IL.Offset(0, -20).Value
            End If

            End If

        End If
        End If
        End If
'        MsgBox "Börjar nästa rad, rad" & rRow_IL.Address
        Next rRow_IL
'    Next rCol_IL
            End If
            
'        Nu utanför den inre loopen (alltså klar med kontrollen av den aktuella referenscellen
        
'        Else
'        MsgBox "Kommer inte köra" & ws.Name
'            End If
'            Ebnd
                Next rRow_OL
            Next rCol_OL
 End If
 Next ws
            

End Sub

Sry for the swedish notes and comments in the code.

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