• Home
 · 
• Math/Dates
 · 
• Linear Regression - Give the function two arrays of data and it will return the equation for the li

Linear Regression - Give the function two arrays of data and it will return the equation for the li

Linear Regression - Give the function two arrays of data and it will return the equation for the line of best fit. You can then adapt the code

Rate Linear Regression - Give the function two arrays of data and it will return the equation for the li

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'------------------------------------------------------
' Many thanks to Dr. Thomas Meinike for this code
' Source http://www.basicworld.com/vbplus/tt170898
'------------------------------------------------------
' Mat Berry April 2001 - the guy that did this originally
' did not have it quite right. N was counting 2 too many which
' causes problems with small data sets. It now calcs the same
' formula as MS Excel.
'------------------------------------------------------
    Dim sx      As Double
    Dim sy      As Double
    Dim SZ      As Double
    Dim s2      As Double
    Dim s3      As Double
    Dim i       As Integer
    Dim a       As Double
    Dim b       As Double
    Dim r       As Double
    Dim N       As Integer
    
    N = UBound(x())

    sx = 0: sy = 0: s2 = 0: s3 = 0: SZ = 0
    
    For i = 0 To N - 1
        sx = sx + x(i)
        sy = sy + y(i)
        SZ = SZ + x(i) * y(i)
        s2 = s2 + x(i) * x(i)
        s3 = s3 + y(i) * y(i)
    Next i
    b = (N * SZ - sx * sy) / (N * s2 - sx * sx)
    a = (sy - b * sx) / N
    r = Abs((N * SZ - sx * sy) / ((N * s2 - (sx * sx)) * (N * s3 - (sy * sy))) ^ (1 / 2))
    If b < 0 Then r = (-r)
    If a > 0 Then
        Regression_Linear = "y = " & b & "x + " & a & vbCrLf & "r^2 = " & r
    Else
        Regression_Linear = "y = " & b & "x" & a & vbCrLf & "r^2 = " & r
    End If
End Function