VBA code to calculate the distance between point and the regression line

Lalikos

New Member
Joined
May 16, 2016
Messages
2
Hello guys. I made a function that calculates the distances between the points (of a X,Y table) and the regression line, while you need only the X and Y ranges/tables and of course their values. Here's what it looks like:


=ABS((ABS(SLOPE(J23:J32,I23:I32))*I23-J23+INTERCEPT(J23:J32,I23:I32))/SQRT((SLOPE(J23:J32,I23:I32)^(2)+(1))))


Where J23:J32 & I23:I32 happens to be the Y and X value ranges/tables respectively. And I23 is the X and the J23 is the Y.


So I'm basicaly trying to make a code in VBA for this function so I can use this regularly because of my job but unfortunately, I have no idea. I'd like the function to ask only for the Known X's and Y's (just like the slope function) and the x and y.
Can anyone help please?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,316
Hi and welcome to the MrExcel Message Board.

I may not be understanding this correctly but I am not sure why you have a Sqrt(Slope^2 + 1) in there.

Also, I think I would use FORECAST and not SLOPE and INTERCEPT.

For example:
=ABS(FORECAST(I23,$J$23:$J$32,$I$23:$I$32)-J23)

If you want to do it that way then in VBA you could use:
Code:
Sub Test()

    Dim f As Double
    
    f = WorksheetFunction.Forecast(Range("I23"), Range("J23:J32"), Range("I23:I32")) - Range("J23")
    
    MsgBox f
    
End Sub
 
Last edited:

Lalikos

New Member
Joined
May 16, 2016
Messages
2
Hello sir and thanks for your reply.
Well basicaly I'm working with two variables, an Input and a Target variable. I create a scatter plot adding a trendline and the R squared, and I try to keep it relatively high (R^2>=0.75). But keeping it high mean that I have to remove some of the outliers from the data tables, and because the points are two-dimensional some of the traditional ways of removing the outliers (such as the Average +/- 3*St.Deviations) doesn't work for me. And what i'm trying to do is to calculate the distance for each point between them and the trendline using the algebric equation |Ax+By+C|/SQRT(A^2+B^2). We already know that B=-1 and also that the A(=SLOPE function) and C(=INTERCEPT function). By calculating the distances I can determine which of the points have the larger distance and so which of the points increase the statistical error. I know that I can erase the point manualy, but I have to justify it for my thesis, and also it works perfectly. Moreover to predict some values that I need, for the moment I use a neural network.
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,316
OK, I think I have got it.

You want t calculate the perpendicular distance between the point and the line and not the vertical (or horizonta) distance.
I think this works but there are all kinds of ways of putting it together.
Code:
Sub Test()

    Dim Ans        As Double
    Dim r1         As Range
    Dim r2         As Range
    Dim slope      As Double
    Dim Intercept  As Double
    Dim X          As Double
    Dim Y          As Double
    
    Set r1 = Range("I23:I32")
    Set r2 = Range("J23:J32")
    
    slope = WorksheetFunction.slope(r2, r1)
    Intercept = WorksheetFunction.Intercept(r2, r1)
    X = Range("I23").Value
    Y = Range("J23").Value
    
    Ans = Abs((slope * X - Y + Intercept) / Sqr(slope ^ 2 + 1))
    MsgBox Ans
    
End Sub
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,775
Office Version
  1. 2010
Platform
  1. Windows
Why do you want to calculate the perpendicular distance of the points from the regression line as a measure of determining outliers, rather than vertical distance, which is what the regression minimizes?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,852
Messages
5,574,663
Members
412,608
Latest member
Guromir
Top