Solve 2nd simultaneous eqns

[ykpotdar]

Hi,

I know coordinates of two points (x & y ) and want to find the center of the circle of radisu R (known) passing through these points. How can I get EXCEL to do it? We can assume that R, x and y are three cells in a EXCEL spreadsheet. I want to do this for about 100 points.

Thanks,

-Yogesh

[Jay Petrulis]

Deleted, duplicate response.

[Jay Petrulis]

Hi,

This is untested, so try this out on your data and report your results. It will find 1 of the 2 solutions (it should correctly identifythe situations where 0, 1, or infinite solutions are possible).

The return_type should be an "x" or a "y" string.

Function FindCircleCenter(Return_Type As String, Radius As Double, _
                          x_0 As Double, y_0 As Double, _
                          x_1 As Double, y_1 As Double)


Dim MidptDist As Double
Dim x_coordinate As Double
Dim y_coordinate As Double
Dim x_mid As Double
Dim y_mid As Double
Dim Slope_Test As Double
Dim Orthogonal_Slope As Double
Dim Triangle_Height As Double
Dim Theta As Double

MidptDist = Distance_Between_Points(x_0, y_0, x_1, y_1) / 2
x_mid = Midpoint_Between_Points("X", x_0, y_0, x_1, y_1)
y_mid = Midpoint_Between_Points("Y", x_0, y_0, x_1, y_1)
Triangle_Height = Sqr(Radius ^ 2 - MidptDist ^ 2)

If MidptDist > Radius Then
    FindCircleCenter = "no solution"
    Exit Function
ElseIf MidptDist = Radius Then
    x_coordinate = x_mid
    y_coordinate = y_mid
    GoTo ExitTheFunction
ElseIf MidptDist = 0 Then
    FindCircleCenter = "infinite solutions"
    Exit Function
End If


If x_0 = x_1 Then
    x_coordinate = x_0 + Triangle_Height
    y_coordinate = y_mid
    GoTo ExitTheFunction
End If
Slope_Test = Slope_Between_Points(x_0, y_0, x_1, y_1)
If Slope_Test = 0 Then
    x_coordinate = x_mid
    y_coordinate = y_mid + Triangle_Height
    GoTo ExitTheFunction
Else
    Orthogonal_Slope = -1 / Slope_Test
    Theta = Atn(Orthogonal_Slope)
    x_coordinate = x_mid + Triangle_Height * Cos(Theta)
    y_coordinate = y_mid + Triangle_Height * Sin(Theta)

End If

ExitTheFunction:

Select Case UCase(Left(Return_Type, 1))
    Case Is = "X": FindCircleCenter = x_coordinate
    Case Is = "Y": FindCircleCenter = x_coordinate
    Case Else: FindCircleCenter = CVErr(xlErrValue)
End Select

End Function

Function Distance_Between_Points(x_0 As Double, y_0 As Double, _
                                 x_1 As Double, y_1 As Double, _
                                 Optional z_0 As Double = 0, _
                                 Optional z_1 As Double = 0)
Dim x As Double
Dim y As Double
Dim z As Double

x = (x_1 - x_0) ^ 2
y = (y_1 - y_0) ^ 2
z = (z_1 - z_0) ^ 2
                        
                        
Distance_Between_Points = Sqr(x + y + z)
End Function

Function Slope_Between_Points(x_0 As Double, y_0 As Double, _
                              x_1 As Double, y_1 As Double)
    
Slope_Between_Points = (y_1 - y_0) / (x_1 - x_0)
End Function

Function Midpoint_Between_Points(Return_Type As String, _
                                 x_0 As Double, y_0 As Double, _
                                 x_1 As Double, y_1 As Double, _
                                 Optional z_0 As Double = 0, _
                                 Optional z_1 As Double = 0)
                        
Dim x As Double
Dim y As Double
Dim z As Double
                        
x = (x_1 - x_0) / 2
y = (y_1 - y_0) / 2
z = (z_1 - z_0) / 2

Select Case UCase(Left(Return_Type, 1))
    Case Is = "X": Midpoint_Between_Points = x_0 + x
    Case Is = "Y": Midpoint_Between_Points = y_0 + y
    Case Is = "Z": Midpoint_Between_Points = z_0 + z
    Case Else: Midpoint_Between_Points = CVErr(xlErrValue)
End Select

End Function

[Egress1]

Sure Jay, take the easy ones why don't ya!

[SIXTH SENSE]

Originally Posted by ykpotdar

Hi,

I know coordinates of two points (x & y ) and want to find the center of the circle of radisu R (known) passing through these points. How can I get EXCEL to do it? We can assume that R, x and y are three cells in a EXCEL spreadsheet. I want to do this for about 100 points.

Thanks,

-Yogesh

hi!
do you mean that X is a point with corrdiante(x1,y1) and Y is another point with coordiante (x2,y2)

[Jay Petrulis]

Hi,

In the code, I have the following...

Select Case UCase(Left(Return_Type, 1)) 
    Case Is = "X": FindCircleCenter = x_coordinate 
    Case Is = "Y": FindCircleCenter = x_coordinate 
    Case Else: FindCircleCenter = CVErr(xlErrValue) 
End Select

The "Y" case should read

Case Is = "Y": FindCircleCenter = y_coordinate

Sorry about that.

[Seti]

In most cases, aren't there 2 solutions? Once one is found, there should be another located triangle hieght away from the line segment joinging x and y in the opposite direction of the first solution. I didn't follow all of Jay's code, so if there are two solutions falling out of this, I apologize.

[Jay Petrulis]

Hi Seti,

In my preamble, I note that there are two solutions in most cases, but my (untested) function only returns one of them (a solution in quadrants I or IV).

If I get some time today, I will give it some testing and report back. I hope the OP gives it a more thorough test with the actual data used.

[Jay Petrulis]

Originally Posted by Egress1

Sure Jay, take the easy ones why don't ya!

Hi Ken,

It may be easy for you, but I got confused writing this at first and so decided to separate each piece into its own custom function.

Actually, I think that is the way to go in many instances, so I am going to keep to that strategy from now on. It may not be the absolutely most efficient way in terms of computer performance, but it is the most flexible and adaptable.

[Egress1]

Hi Jay,

I was being sarcastic my friend. I am most impressed by your knowledge (not sarcastic).