Private Sub LineTrendFormula(ByRef y() As Double, ByRef b As Double, ByRef m As Double, Optional ByRef r2 As Double)
' данная функция возвращает коэффициенты для линейного тренда
' формула y = m * x + b
' вызов функции LineTrendFormula y, b, m, r2
' на входе массив значений
' на выходе свободный коэф, коэф x, достоверность
'
    Dim x() As Integer:    ReDim x(LBound(y) To UBound(y)) As Integer
    For i = LBound(x) To UBound(x):          x(i) = i:    Next i
    Dim Coefs As Variant: Coefs = Application.LinEst(y, x, , 1)
    b = Coefs(1, 2)
    m = Coefs(1, 1)
    r2 = Coefs(3, 1)
End Sub

Private Sub LogarithmTrendFormula(ByRef y() As Double, ByRef b As Double, ByRef c As Double, Optional ByRef r2 As Double)
' данная функция возвращает коэффициенты для логарифмического тренда
' формула y = (c * LN(x)) + b
' вызов функции LogarithmTrendFormula y, b, с, r2
' на входе массив значений
' на выходе свободный коэф, коэф ln(x), достоверность
'
    Dim x() As Double:    ReDim x(LBound(y) To UBound(y)) As Double
    For i = LBound(x) To UBound(x):          x(i) = Application.Ln(i):    Next i
    Dim Coefs As Variant:    Coefs = Application.LinEst(y, x, , 1)
    b = Coefs(1, 2)
    c = Coefs(1, 1)
    r2 = Coefs(3, 1)
End Sub

Private Sub StepenTrendFormula(ByRef y() As Double, ByRef b As Double, ByRef c As Double, Optional ByRef r2 As Double)
' данная функция возвращает коэффициенты для степенного тренда
' формула y=c*x^b
' вызов функции StepenTrendFormula y, b, c, r2
' на входе массив значений
' на выходе степень, коэф x, достоверность
'
    Dim x() As Double, y1() As Double: ReDim x(LBound(y) To UBound(y)) As Double: ReDim y1(LBound(y) To UBound(y)) As Double
    For i = LBound(x) To UBound(x): x(i) = Application.Ln(i): y1(i) = Application.Ln(y(i)): Next i
    Dim Coefs As Variant:    Coefs = Application.LinEst(y1, x, , 1)
    b = Coefs(1, 1)
    c = Exp(Coefs(1, 2))
    r2 = Coefs(3, 1)
End Sub

Private Sub ExpTrendFormula(ByRef y() As Double, ByRef b As Double, ByRef c As Double, Optional ByRef r2 As Double)
' данная функция возвращает коэффициенты для экспоненциального  тренда
' формула y = c * e ^(b * x) что аналогично y = c * exp(b * x)
' вызов функции ExpTrendFormula y, b, с, r2
' на входе массив значений
' на выходе коэффициент степени x, коэф е, достоверность
'
    Dim x() As Double, y1() As Double
    ReDim x(LBound(y) To UBound(y)) As Double
    ReDim y1(LBound(y) To UBound(y)) As Double
    For i = LBound(x) To UBound(x): x(i) = i: y1(i) = Application.Ln(y(i)): Next i
    Dim Coefs As Variant:    Coefs = Application.LinEst(y1, x, , 1)
    b = Coefs(1, 1)
    c = Exp(Coefs(1, 2))
    r2 = Coefs(3, 1)
End Sub

Private Sub PolyTrendFormula(ByRef y() As Double, ByVal PolyStep As Integer, ByRef b As Double, ByRef c() As Double, Optional ByRef r2 As Double)
' данная функция возвращает коэффициенты для полинома любой степени
' пример формулы полинома второй степени y = (c2 * x^2) + (c1 * x ^1) + b
' пример формулы полинома третьей степени y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
' пример формулы полинома четвертой степени y = (c4 * x^4)+(c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
' на входе массив значений, степень полинома
' на выходе свободный коэф, массив степенных коэф, достоверность
'
' ниже пример использования в коде
'Dim y() As Double, b As Double, r2 As Double, c() As Double
' .....генерация одномерного массива y.....
'PolyTrendFormula y, 4, b, c, r2
'
'Debug.Print b
'For i = 1 To UBound(c)
'    Debug.Print c(i)
'Next i
'Debug.Print r2
    Dim x() As Variant, y1() As Double
    ReDim x(LBound(y) To UBound(y), 1 To PolyStep) As Variant
    ReDim y1(LBound(y) To UBound(y), 1 To 1) As Double
    ReDim c(1 To PolyStep) As Double
    For i = LBound(x) To UBound(x): For n = 1 To PolyStep: x(i, n) = i ^ n: Next n: y1(i, 1) = y(i): Next i
    
    Dim Coefs As Variant
        Coefs = Application.LinEst(y1, x, , 1)
    
    b = Coefs(1, PolyStep + 1)
    For i = PolyStep To 1 Step -1
        c(PolyStep - i + 1) = Coefs(1, i)
    Next i
    r2 = Coefs(3, 1)

End Sub

Public Sub CreateTrends(ByRef InArray() As Double, ByVal Forec As Long)
    On Error GoTo 1
    ' архивируем старый массив
    Dim TmpArr() As Double: ReDim TmpArr(LBound(InArray) To UBound(InArray)) As Double
    For i = LBound(InArray) To UBound(InArray): TmpArr(i) = InArray(i): Next i
    Erase InArray: ReDim InArray(LBound(TmpArr) To UBound(TmpArr) + Forec, 1 To 8) As Double
    
    ' строим формулы тренда
    Dim LinB As Double, LinM As Double, LogB As Double, LogC As Double, StepB As Double, StepC As Double
    Dim ExpB As Double, ExpC As Double, Poly2B As Double, Poly3B As Double, Poly4B As Double
    Dim Poly2C() As Double, Poly3C() As Double, Poly4C() As Double
1:
On Error Resume Next
    LineTrendFormula TmpArr, LinB, LinM
    LogarithmTrendFormula TmpArr, LogB, LogC
    StepenTrendFormula TmpArr, StepB, StepC
    ExpTrendFormula TmpArr, ExpB, ExpC
    PolyTrendFormula TmpArr, 2, Poly2B, Poly2C
    PolyTrendFormula TmpArr, 3, Poly3B, Poly3C
    PolyTrendFormula TmpArr, 4, Poly4B, Poly4C
    
    ' считаем тренды и засовываем их в массив
    For i = LBound(InArray) To UBound(InArray)
         If i <= UBound(TmpArr) Then InArray(i, 1) = TmpArr(i)      ' записываем сам массив фактов
         ' затем сами тренды записываем в 2-8 столбцы массива
         InArray(i, 2) = LinM * i + LinB
         InArray(i, 3) = (LogC * Application.Ln(i)) + LogB
         InArray(i, 4) = StepC * i ^ StepB
         InArray(i, 5) = ExpC * Exp((ExpB * i))
         InArray(i, 6) = (Poly2C(2) * i ^ 2) + (Poly2C(1) * i ^ 1) + Poly2B
         InArray(i, 7) = (Poly3C(3) * i ^ 3) + (Poly3C(2) * i ^ 2) + (Poly3C(1) * i ^ 1) + Poly3B
         InArray(i, 8) = (Poly4C(4) * i ^ 4) + (Poly4C(3) * i ^ 3) + (Poly4C(2) * i ^ 2) + (Poly4C(1) * i ^ 1) + Poly4B
    Next i
Erase TmpArr
End Sub