I need an Excel formula that can help me find the third point in a triangle, given most other information. This is not homework, meaning I do not need to do this just once. I need a consistent way to accomplish it repeatedly over the next few months, as quickly and accurately as possible, so I am hoping Excel or something similar will be the best tool. I am under the impression that there may always be two valid answers to this problem. If so, and if possible, four formulas, two for the possible X values, and two for the possible Y values, would be fine. In most cases, working out which is correct will not be difficult. My main concern is being able to do this accurately, repeatedly.
All points are on a 2D Cartesian system, (have X and Y values), and can be negative or positive. The triangle will be random, and so must be assumed to be scalene. For each triangle, I have two Cartesian points, along with the distances from those points to the third point. From this I can easily ensure that in the other columns, I have the lengths of all three sides, and all three inner angles in both radian and degree formats, as well as the two Cartesian points as X and Y values. How can I use an Excel formula to come up with the remaining X and Y values of the two valid third points?
I’ll lay out the columns / variables that I have here to be clear, in case you’d like to use my letters rather than traditional notation.
point 1:
A = X1
B = Y1
point 2:
C = X2
D = Y2
E = distance from p1 to p2 (calculated from other values)
F = distance from p1 to p3
G = distance from p2 to p3
H = inner angle at p1 in radians (calculated from other values)
I = inner angle at p2 in radians (calculated from other values)
J = inner angle at p3 in radians (calculated from other values)
The cells that will contain my formulas:
K = possible X3
L = other possible X3
M = possible Y3
N = other possible Y3
My assumption is that there would be two formulas for X3, and two formulas for Y3, each producing a possible solution, but I’m not 100% sure if this is true.
I can’t quite handle the math, but my instinct would be that I could somehow grab one of my distance formulas, for example the one to calculate distance E:
=SQRT(((C-A)^2)+((D-B)^2))
and manipulate it to solve for the one X,Y set, given distance and the other X,Y set, but each time I attempt that, I get lost or make an error before I find a functioning formula.
Is this possible to do in a simple way using the columns / variables above and a set of four Excel formulas? Thank you.
Here is one possible set of formulas, that does basically what Dane described. Tested in Google Docs.
A= ____ // X1
B= ____ // Y1
C= ____ // X2
D= ____ // Y2
E= sqrt((A-C)^2+(B-D)^2) // Distance 12
F= ____ // Distance 13
G= ____ // Distance 23
H= acos((E^2+F^2-G^2)/(2*E*F)) // Angle 1
I= acos((E^2+G^2-F^2)/(2*E*G)) // Angle 2
J= acos((F^2+G^2-E^2)/(2*F*G)) // Angle 3
K= A+F*cos(atan2(C-A,D-B)+H) // X3
L= A+F*cos(atan2(C-A,D-B)-H) // X3Alt
M= B+F*sin(atan2(C-A,D-B)+H) // Y3
N= B+F*sin(atan2(C-A,D-B)-H) // Y3Alt
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments