Excel Matrix
Excel Matrix
(OP)
I'm trying to solve for a plane's coordinates based off 3 points. I have the 3 points. I found the equations for solving for this, but I'm not sure how to enter this into excel to yield the results I'll need. Please let me know. Thanks.
http://en.wikipedia.org/wiki/Plane_(mathematics)
any help?
http://en.wikipedia.org/wiki/Plane_(mathematics)
any help?
RC
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
www.tbastructures.com





RE: Excel Matrix
CODE
'P is an 3x3 range, with the coordinates of 3 points that define the plane, set up as follows (example coordinates)
' p1 p2 p3
'x 1 2 8
'y 2 5 5
'z 3 7 5
Dim Z As Double, D(6) As Double
'set up the matrix
Dim A(3, 3) As Double
A(1, 1) = X - P(1, 1)
A(1, 2) = Y - P(2, 1)
A(2, 1) = P(1, 2) - P(1, 1)
A(2, 2) = P(2, 2) - P(2, 1)
A(2, 3) = P(3, 2) - P(3, 1)
A(3, 1) = P(1, 3) - P(1, 1)
A(3, 2) = P(2, 3) - P(2, 1)
A(3, 3) = P(3, 3) - P(3, 1)
'A(1, 3) = Z - P(3, 1)
D(1) = A(1, 1) * A(2, 2) * A(3, 3)
D(2) = A(1, 1) * A(2, 3) * A(3, 2)
D(3) = A(1, 2) * A(2, 1) * A(3, 3)
D(5) = A(1, 2) * A(2, 3) * A(3, 1)
D(0) = D(1) - D(2) - D(3) + D(5)
A(1, 3) = -D(0) / (A(2, 1) * A(3, 2) - A(2, 2) * A(3, 1))
Z = A(1, 3) + P(3, 1)
CalcZFromGivenXY = Z
End Function
p1 p2 p3
x 1 2 8
y 2 5 5
z 3 7 5
(p1 coordinates in B2:B4 etc.), then you can calculate the Z coordinate for any given pair of X,Y coordinates by =CalcZFromGivenXY($B$2:$D$4, Your_X , Your_Y )
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel Matrix
RC
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
www.tbastructures.com
RE: Excel Matrix
CODE
| x1 y1 z1 1 | = 0
| x2 y2 z2 1 |
| x3 y3 z3 1 |
Should work!
RE: Excel Matrix
CODE
| y2 z2 1 |x + | z2 x2 1 |y +
| y3 z3 1 | | z3 x3 1 |
| x1 y1 1 | | x1 y1 z1 |
| x2 y2 1 |z - | x2 y2 z2 | = 0
| x3 y3 1 | | x3 y3 z3 |
Reference: Mathematical handbook for scientists and Engineers by Korn and Korn, 1961, page 65.
Never trust wiki... it's just something written by somebody without any credentials.
RE: Excel Matrix
A(X1,Y1,Z1)
B(X2,Y2,Z2)
C(X3,Y3,Z3)
I have all the coordinates for the 3 points. I'm looking for a way to solve for the Plane formula (a,b,c,d) in Excel using matrices. I dont want to have to type the codes in and use any goal seek or solver. I want it to be as automated as possible. This will come in handy for our stair designs, which have differential deflection across platforms, etc.
RC
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
www.tbastructures.com
RE: Excel Matrix
Cheers
Greg Locock
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel Matrix
When you call the function, replace YourX and YourY with the X and Y values of the point that you need the z coordinate for. So, if you need Z at x=0, y=1 , call the function from a spreadsheet like: =CalcZFromGivenXY($B$2:$D$4, 0, 1)
Yakpol,
The way to calculate the determinant of the matrix in your first post is what you give in your second post. I am not sure if there shouldn't be a minus sign in the second term |...|*y (maybe that's handled by the swapping of the first and second column, i.e. x1 and z1?)
As Greg points out, the determinants given in your second post can be calculated easily by using MDETERM; just set up the matrices correctly.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel Matrix
RC
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
www.tbastructures.com
RE: Excel Matrix
CODE
A B C
1| x1 y1 z1
2| x2 y2 z2
3| x3 y3 z3
define plane that may be expressed by equation
z = Py + Qx + R
where
P = INDEX(LINEST(C1:C3,A1:B3),1)
Q = INDEX(LINEST(C1:C3,A1:B3),2)
R = INDEX(LINEST(C1:C3,A1:B3),3)