×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Matrix

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?

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

www.tbastructures.com

RE: Excel Matrix

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:

CODE

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:
    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

(OP)
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

www.tbastructures.com

RE: Excel Matrix

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

CODE

| 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(A1:D4) (don't forget CTR-SHIFT-ENTER )to zero.

Should work!

RE: Excel Matrix

I found even better solution without using goal seeker.

CODE

| 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.

RE: Excel Matrix

(OP)
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

www.tbastructures.com

RE: Excel Matrix

Check out MDETERM

Cheers

Greg Locock

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

RE: Excel Matrix

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.

RE: Excel Matrix

(OP)
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

www.tbastructures.com

RE: Excel Matrix

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

CODE

3 known points in cells A1:C3
     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)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources