+ Reply to Thread
Results 1 to 7 of 7

Perpendicular distance from linear regression line to a point?

  1. #1
    Winny
    Guest

    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


  2. #2
    Bernie Deitrick
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  3. #3
    Bernard Liengme
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  4. #4
    Bernie Deitrick
    Guest

    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:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >>

    >
    >




  5. #5
    Harlan Grove
    Guest

    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).


  6. #6
    Bernard Liengme
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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).
    >




  7. #7
    Herbert Seidenberg
    Guest

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1