Thanks P45, I'll give it a go.
Mike,
A much neater explanation!
Thanks P45, I'll give it a go.
Mike,
A much neater explanation!
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Here's my approach,
we are given 3 points (P1, P2, P3)
(These are vectors, each Pi has 3 coordinates)
We have a (partialy) unknown V =(given X, given Y, unknown Z)
Calculate
Q1 = P1-V
Q2 = P2-V
Q3 = P3-V
Then take the inner products
Q1 • Q2
Q1 • Q3
Q2 • Q3
And divide by the product of the lengths of Qi, Qj to give us the cosines of the angles between Qi and Qj
(Q1 • Q2) / Len(Q1) / Len(Q2)
(Q1 • Q3) / Len(Q1) / Len(Q3)
(Q2 • Q3) / Len(Q2) / Len(Q3)
Sum the arcsines of these values.
If V is interior to the triangle P1 P2 P3, i.e. lies in the plane defined by the 3 points, then the sum of those arcsines = 360°
So use Solver to find the Z value that will make that sum = 360
(Edit: Corrected this line and attached spreadsheet)
This method has significant round-off error. On one test, Excel returned .998 when 1 was the answer.
Last edited by mikerickson; 12-11-2009 at 10:23 PM.
I've found a more robust approach. It
a) does not require that X be interior to the triangle.
b) avoids dividing by lenghts (a.k.a round off error)
It utilizes the cross-product and uses Solver to find the Z, such that Q3 • (Q1 X Q2) = 0
If the Button doesn't work, Solver can be called manualy or the Solver Add-in should be active in the VBE Tools>References.
Doing this as a UDF is another way.
I set it to pass ranges, but you can easily change that or make it a sub,
[vba]
'ref: http://www.jtaylor1142001.net/calcja...nes/VP3Pts.htm
'P,Q,R as 3 Col by 1 Row, X as 2 Col, 1 Row
Function Triang(P As Range, R As Range, Q As Range, X As Range) As Variant
Dim i As Long
Dim Z As Double
Dim PQ(1 To 1, 1 To 3) As Double, PR(1 To 1, 1 To 3) As Double, PQxPR(1 To 1, 1 To 3) As Double
Dim Coeff(1 To 1, 1 To 3) As Double, D As Double
On Error GoTo NiceExit
'get vectors PQ and PR
For i = 1 To 3
PQ(1, i) = Q(1, i) - P(1, i)
PR(1, i) = R(1, i) - P(1, i)
Next i
'Normal vector to PQ and PR via cross product
PQxPR(1, 1) = PR(1, 2) * PQ(1, 3) - PR(1, 3) * PQ(1, 2)
PQxPR(1, 2) = PR(1, 3) * PQ(1, 1) - PR(1, 1) * PQ(1, 3)
PQxPR(1, 3) = PR(1, 1) * PQ(1, 2) - PR(1, 2) * PQ(1, 1)
'use first point
D = 0#
For i = 1 To 3
D = D + PQxPR(1, i) * P(1, i).Value
Next i
'solve aX + bY + cZ = D for Z, given X and Y (passed from X range), a,b, and c from cross product, and D solved above
With X
Z = D - (PQxPR(1, 1) * .Cells(1, 1)) - (PQxPR(1, 2) * .Cells(1, 2))
Z = Z / PQxPR(1, 3)
End With
Triang = Z
Exit Function
NiceExit:
Triang = CVErr(xlErrNA)
End Function
[/vba]
Paul