Solved: Triangulation help needed.

[mikerickson]

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.

[mikerickson]

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.

[Paul_Hossler]

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