Perpendicular distance from linear regression line to a point?

[Winny]

Hi all,

First post for me on here, I have also posted this on the excel
charting group.

I have some Excel charts, with least squares fit lines through the
data. On each chart I have a single extra data point (it's own series)
and I want to know the perpendicular distance from the line to this
point.

This is a physical distance in the real world, as my axes are both in
milimeters but to different scales.

Thanks for any help.

--Winny

 

[Bernie Deitrick]

Winny,

The distance from point (X', Y') to line Ax + By + C = 0 is

=ABS((AX'+BY'+C) / SQRT(A^2 + B^2))

Often, line equations are written as

Y = mX + b, where m is the slope and b is the intersect. In that case, for line Ax + By + C = 0, A =
m, B = -1, and C = b.

HTH,
Bernie
MS Excel MVP

 

[Bernard Liengme]

Hope you are ready for algebra lesson this morning!
The line of best fit for the data I will assume has equation y =
m(1)x+b(1) - read the (1) as subscripts
Values m(1) and b(1) are known to you from the SLOPE and INTERCEPT functions
(or the LINEST function)
The line of the perpendicular has the equation y=m(2)x+b(2)
But since the lines are perpendicular m(2) = 1/(m(1) so equation is
y=[1/m(1)]x+b(2)
The single point you refer to has coordinates X(2),Y(2) and these values are
known to you
The point where the perpendicular line meets the coordinates X(1), Y(1)

From equation of second line y=[1/m(1)]x+b(2) and known X(2) and Y(2) values
find b(2)
b(2) = Y(2) - [1/m(1)]X(2)

The value of x at the intercept of the two lines is X(1)
and as the point is on both lines y of line 1 = y of line 2
[1/m(1)]X(1)+b(2) = m1X(1)+b(1); solve this for X(1)
We can now find Y(1) from equation Y(1) = [1/m(1)]X(1)

We already knew X(2) and Y(2) and now we know X(1) and Y(1)

The distance between these two points is Sqrt( (X(1)-X(2))^2 +
(Y(1)-Y(2))^2 )

Is this homework? If not send me (send to me not group) the file and we can
compare results.

best wishes

 

[Bernie Deitrick]

Winny,

Here is a formula version, with your known X's in A1:A10, your known Y's in B1:B10, X' in D1, and Y'
in D2:

=ABS((SLOPE(B1:B10,A1:A10)*D1-D2+INTERCEPT(B1:B10,A1:A10))/SQRT(SLOPE(B1:B10,A1:A10)^2 +1))

HTH,
Bernie
MS Excel MVP

 

[Harlan Grove]

Bernard Liengme wrote...
....

The line of best fit for the data I will assume has equation
y = m(1)x+b(1) - read the (1) as subscripts
Values m(1) and b(1) are known to you from the SLOPE and INTERCEPT functions ....
But since the lines are perpendicular m(2) = 1/(m(1) so equation is
y=[1/m(1)]x+b(2)

....

The slope of the line perpendicular to y = m(1) x + b is -1/m(1), so
its equation should be y = -x/m(1) + b(2).

From equation of second line y=[1/m(1)]x+b(2) and known X(2) and Y(2) values
find b(2)
b(2) = Y(2) - [1/m(1)]X(2)

But the equation should be

b(2) = y(2) + x(2)/m(1)

Consider the intersection of the lines given by

y = 2 x + 3

and

y = -x / 2 + 5

so

2 x + 3 = -x / 2 + 5
2.5 x = 2
x = .8
y = 4.6

As for perpendicular distance, a little trigonometry goes a long way.
In general, if you know the slope of the line and the (x,y) coordinates
of the point, the vertical distance between them is ABS(y - m x - b),
and the perpendicular distance is

ABS(y - m x - b) COS(ATAN(m))

Using the first line above and the point (1,1), the point on the
perpendicular line passing through (1,1) is (-.6,1.8) and the
perpendicular distance is 1.788854382. Alternatively, if you have the
point on the line through which the perpendicular lines passes that
also passes through the other point, the distance may be calculated
directly as SQRT((X(1)-X(2))^2+(Y(1)-Y(2))^2).

 

[Bernard Liengme]

Thanks H, next time I'll wait until I'm awake!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Bernard Liengme wrote...
...

The line of best fit for the data I will assume has equation
y = m(1)x+b(1) - read the (1) as subscripts
Values m(1) and b(1) are known to you from the SLOPE and INTERCEPT
functions ...
But since the lines are perpendicular m(2) = 1/(m(1) so equation is
y=[1/m(1)]x+b(2)

...

The slope of the line perpendicular to y = m(1) x + b is -1/m(1), so
its equation should be y = -x/m(1) + b(2).

From equation of second line y=[1/m(1)]x+b(2) and known X(2) and Y(2)
values
find b(2)
b(2) = Y(2) - [1/m(1)]X(2)

But the equation should be

b(2) = y(2) + x(2)/m(1)

Consider the intersection of the lines given by

y = 2 x + 3

and

y = -x / 2 + 5

so

2 x + 3 = -x / 2 + 5
2.5 x = 2
x = .8
y = 4.6

As for perpendicular distance, a little trigonometry goes a long way.
In general, if you know the slope of the line and the (x,y) coordinates
of the point, the vertical distance between them is ABS(y - m x - b),
and the perpendicular distance is

ABS(y - m x - b) COS(ATAN(m))

Using the first line above and the point (1,1), the point on the
perpendicular line passing through (1,1) is (-.6,1.8) and the
perpendicular distance is 1.788854382. Alternatively, if you have the
point on the line through which the perpendicular lines passes that
also passes through the other point, the distance may be calculated
directly as SQRT((X(1)-X(2))^2+(Y(1)-Y(2))^2).

 

[Herbert Seidenberg]

distance=ABS(y_2-(b_1+m_1*x_2))/SQRT(1+m_1^2)
Copied from
http://mathworld.wolfram.com/LeastSquaresFittingPerpendicularOffsets.html