shortest distance between a point and a line

**stockgoblin42** · 07-04-2013, 11:19 AM

Hi,

I'm trying to formulate the shortest distance between a line and a point.

Please see attahced picture for the formula.

To translate it into Excel I'm having great difficulties and only getting #REF! as an outcome. Here's my attempt.

Please Login or Register  to view this content.

TinyTri!I4 = Px
TinyTri!J4 = Py
Paras!A1 = m
Paras!C1 = c

Does anyone have an eagle eye for bracket placement?

**MrShorty** · 07-04-2013, 12:28 PM

Not sure if I have an "eagle eye" for bracket placement. Not sure a misplaced paranthesis would trigger a ref error, though it's certainly possible. I think you missed an asterix/multiplication symbol midway through (start of the 2nd term).

If that's all it is, the rest of my post could be wasted space, but I know that building this kind of formula is always challenging, and even harder to debug. I know it isn't popular (we tend to think that a single cell is more efficient than multiple cells). I personally reject this definition of efficiency and would build a formula like this with multiple cells. For example, I note that the quantity Px+mPy-mc is used twice in the formula, would be easy to put into its own cell, and would also dramatically simplify the formula in the final cell. I also note that m^2+1 is used twice. I don't know about others' philosophies, but I prefer to program a spreadsheet so that each cell is relatively simple and easy to debug -- even if it means using 3 or 5 or 10 times as many cells as others would use. Even when I do want to eventually combine it all into a single cell, having several cells with the intermediate calculations is useful while building the complex, single cell formula as a means of testing the final result.

**XOR LX** · 07-04-2013, 01:06 PM

Hi,

This part: ...+((Paras!A1((TinyTri!I4...

looks like it needs a mathematical operation of some sort (multiplication?) in there:

...+((Paras!A1*((TinyTri!I4...

Regards

**prem2059** · 07-04-2013, 02:18 PM

Hi stockgoblin42,

Check It and confirm ..

=SQRT((((TinyTri!I4+Paras!A1*TinyTri!J4-Paras!A1*Paras!C1)/(Paras!A1^2+1))-TinyTri!I4)^2+((Paras!A1*((TinyTri!I4+Paras!A1*TinyTri!J4-Paras!A1*Paras!C1)/(Paras!A1^2+1))+Paras!C1-TinyTri!J4)^2))

BoL

Prem