Option Explicit
Option Base 1

Function Least_Square_Method#(RngX As Range, RngY As Range, Optional Point# = 0, Optional Mode$ = 0)
       'Function calculates expected Y value from known X for linear dependence
       Dim X, Y, XY, Xip2, Yip2        'Arrays
       Dim Xav#, Yav#, Angle#, Shift#  'Where Y(x) = Shift + Angle * x
       Dim i%, Size#
       'Array handling
       X = RngX: Y = RngY: Size = UBound(X, 1)
       ReDim XY(Size): ReDim Xip2(Size): ReDim Yip2(Size)
       'Filling arrays with values
       For i = 1 To Size
           XY(i) = X(i, 1) * Y(i, 1)
           Xip2(i) = X(i, 1) * X(i, 1)
           Yip2(i) = Y(i, 1) * Y(i, 1)
       Next i
       'Main calculations
       With Application.WorksheetFunction
           Xav = .Average(X)
           Yav = .Average(Y)
           Angle = (Size * .Sum(XY) - .Sum(X) * .Sum(Y)) / (Size * .Sum(Xip2) - .Sum(X) * .Sum(X))
           Shift = Yav - Xav * Angle
           'Final answer
           Select Case Mode
               Case 0, "Y(x)": Least_Square_Method = Shift + Angle * Point
               Case 1, "Sum X": Least_Square_Method = .Sum(X)
               Case 2, "Sum Y": Least_Square_Method = .Sum(Y)
               Case 3, "Sum XY": Least_Square_Method = .Sum(XY)
               Case 4, "Sum X^2 ": Least_Square_Method = .Sum(Xip2)
               Case 5, "Sum Y^2 ": Least_Square_Method = .Sum(Yip2)
               Case 6, "X average": Least_Square_Method = Xav
               Case 7, "Y average": Least_Square_Method = Yav
               Case 8, "B as angle": Least_Square_Method = Angle
               Case 9, "A as shift": Least_Square_Method = Shift
           End Select
       End With
End Function