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

[Lalikos]

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?

 

[RickXL]

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:

Sub Test()

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

 

[Lalikos]

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]

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.

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]

Cross-posted at VBA code to calculate the distance between point and the regression line [SOLVED]

 

[shg]

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?