# Perpendicular distance from linear regression line to a point?

1. ## Perpendicular distance from linear regression line to a point?

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  Register To Reply

2. ## Re: Perpendicular distance from linear regression line to a point?

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

"Winny" <andrewwinn@hotmail.com> wrote in message
> 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
>  Register To Reply

3. ## Re: Perpendicular distance from linear regression line to a point?

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Winny" <andrewwinn@hotmail.com> wrote in message
> 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
>  Register To Reply

4. ## Re: Perpendicular distance from linear regression line to a point?

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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:uwtXX6rvFHA.2292@TK2MSFTNGP12.phx.gbl...
> 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
>
>
> "Winny" <andrewwinn@hotmail.com> wrote in message
>> 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
>>

>
>  Register To Reply

5. ## Re: Perpendicular distance from linear regression line to a point?

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).  Register To Reply

6. ## Re: Perpendicular distance from linear regression line to a point?

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

"Harlan Grove" <hrlngrv@aol.com> wrote in message
> 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).
>  Register To Reply

7. ## Re: Perpendicular distance from linear regression line to a point?

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