Excel Matrix 1

[PEinVA]

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?

RC
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

http://www.tbastructures.com

 

[joerd]

It's not too hard. From the equations in Wikipedia Plane Math and

http://en.wikipedia.org/wiki/Determinant

you can write all the formulas into a spreadsheet or a VB function - which is what I did. It is fairly sloppy, but it does the work:

Function CalcZFromGivenXY(P As Range, X As Double, Y As Double) As Double
'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

If your spreadsheet is setup like:[tt]
p1 p2 p3
x 1 2 8
y 2 5 5
z 3 7 5
[/tt]
(p1 coordinates in B2:B4 etc.), then you can calculate the Z coordinate for any given pair of X,Y coordinates by [tt]=CalcZFromGivenXY($B$2:$D$4, Your_X , Your_Y )[/tt]

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[PEinVA]

I'm not sure I understand how your VB code works. It doesn't seem that X,Y are ever defined or solved. Then in the code to enter into a cell in Excel, you have Your_X and Your_Y. I need these to be determined by the matrix. Maybe I'm sounding really dumb right now, but I'm lost and not sure where I should be heading.

RC
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

http://www.tbastructures.com

 

[yakpol]

There's a more convinient form describing plane by three points than given in wiki.

| x  y  z  1 |
| x1 y1 z1 1 | = 0
| x2 y2 z2 1 |
| x3 y3 z3 1 |

Assuming that you know x and y, find z using tools-goalseeker setting z as a variable and cell with formula for determinant of above matrix =MDETERM(A14) (don't forget CTR-SHIFT-ENTER )to zero.

Should work!

 

[yakpol]

I found even better solution without using goal seeker.

| y1 z1 1 |    | z1 x1 1 | 
| 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 |

From this equation you can write a direct formula for x, y or z.
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.

 

[PEinVA]

I have 3 points
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

http://www.tbastructures.com

 

[GregLocock]

Check out MDETERM

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[joerd]

RCraine,

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.

 

[PEinVA]

I figured it out. It was pretty simple once I got there. Thanks for all the help.

RC
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

http://www.tbastructures.com

 

[doejohn]

It's a little late, but FWIW, here's another approach using the LINEST function:

3 known points in cells A1:C3
[u]     A   B   C [/u]
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)

Using LINEST this way gives a least-squares best-fit plane to 3 or more known points. If the regression is on exactly 3 points as shown, the "fit" is exact.