We made a little update to The Wave Matrix Code.

Makes it possible to extend the Range beyond the available data and produce the Wave Matrix.

This allows new data to be added and the Wave Matrix to update automagically.

https://member.lotterypost.com/jadelottery/images/TheWaveMatrixModuleFunctionCode-01.txt

_______________________________________________________________________________________________________________

Function TheWaveMatrix(ByVal theRange As Range, _
                       ByVal waves As Long, _
                       ByVal iterations As Integer, _
                       ByVal precision As Integer) As Variant()
   
    On Error GoTo ExitFunction
   
    Dim Er(0) As Variant: Er(0) = "Error"
    Dim i, j, k, items, mn, mx, n As Long
   
    n = theRange.Rows.Count
    i = 0
   
    Do
        i = i + 1
    Loop Until (Not Application.WorksheetFunction.IsNumber(theRange.Cells(i, 1))) Or (i = n)
   
    If (Not Application.WorksheetFunction.IsNumber(theRange.Cells(i, 1))) Then i = i - 1
   
    items = i
   
    If (theRange.Columns.Count > 1) Then Er(0) = " Too Many Columns Selected": GoTo ExitFunction
    If (items < 3) Then Er(0) = " Data Selection Error or Too Few Data Points": GoTo ExitFunction
    If (waves < 1) Then Er(0) = " # of Waves < 1": GoTo ExitFunction
    If (iterations < 1) Then Er(0) = " # of Iterations < 1": GoTo ExitFunction
    If (precision < 1) Or (precision > 8) Then Er(0) = " Precision = {1, 2, 3, 4, 5, 6, 7, 8}": GoTo ExitFunction
   
    Dim matrix() As Variant: ReDim matrix(1 To n, 1 To (waves + 2)): For i = 1 To n: For j = 1 To (waves + 2): matrix(i, j) = "": Next j: Next i
    Dim arry() As Double: ReDim arry(1 To items): For i = 1 To items: arry(i) = theRange.Cells(i, 1): Next i
    Dim a, b, degree, sum_y, sum_xy, avg_x, avg_y, avg_xx, avg_xy As Double
    Dim amplitude_degree, amplitude_degree_precision As Double
    Dim frequency_degree, frequency_degree_precision As Double
    Dim degree_precision As Double: degree_precision = 1
    Dim optimal_found, optimal, last_optimal_state, arry_zeroed As Boolean
    Dim arry_up() As Double: ReDim arry_up(1 To items)
    Dim arry_down() As Double: ReDim arry_down(1 To items)
    Dim arry_average() As Double: ReDim arry_average(1 To items)
    Dim arry_sqr_sum, BMA_arry_sqr_sum, RMS_arry, RMS_BMA_arry As Double
    Dim frequency() As Long: ReDim frequency(1 To items)
    Dim diff_frequency() As Long: ReDim diff_frequency(1 To items - 1)
    Dim diff_diff_frequency() As Long: ReDim diff_diff_frequency(1 To items - 2)
    Dim frequency_set(1 To 3) As Long
   
    For i = 1 To precision
        degree_precision = degree_precision / 10
    Next i
   
    sum_y = 0
    sum_xy = 0
   
    For i = 1 To items
        sum_y = sum_y + arry(i)
        sum_xy = sum_xy + i * arry(i)
    Next i
   
    avg_y = sum_y / items
    avg_xy = sum_xy / items
   
    avg_x = (items + 1) / 2
    avg_xx = (items + 1) * (2 * items + 1) / 6
   
    b = (avg_xy - avg_x * avg_y) / (avg_xx - avg_x * avg_x)
    a = avg_y - (b * avg_x)
   
    For i = 1 To items
        matrix(i, 1) = a + (b * i)
        arry(i) = arry(i) - matrix(i, 1)
    Next i
   
    arry_zeroed = False
   
    For k = 1 To waves
   
        sum_y = 0
       
        For i = 1 To items
            sum_y = sum_y + Abs(arry(i))
        Next i
   
        If (sum_y = 0) Then arry_zeroed = True
   
        If arry_zeroed Then
       
            For i = 1 To items
                matrix(i, k + 1) = arry(i)
            Next i
       
        Else
       
            amplitude_degree = 0
            amplitude_degree_precision = 1
            optimal_found = False
   
            For i = 1 To items
                arry_average(i) = arry(i)
            Next i
       
            For j = 1 To iterations
                arry_up(1) = arry_average(1)
           
                For i = 2 To items
                    arry_up(i) = (arry_up(i - 1) + Exp(amplitude_degree) * arry_average(i)) / (1 + Exp(amplitude_degree))
                Next i
           
                arry_down(items) = arry_average(items)
           
                For i = (items - 1) To 1 Step -1
                    arry_down(i) = (arry_down(i + 1) + Exp(amplitude_degree) * arry_average(i)) / (1 + Exp(amplitude_degree))
                Next i
           
                For i = 1 To items
                    arry_average(i) = (arry_up(i) + arry_down(i)) / 2
                Next i
            Next j
   
            arry_sqr_sum = 0
            BMA_arry_sqr_sum = 0
            RMS_arry = 0
            RMS_BMA_arry = 0
       
            For i = 1 To items
                arry_sqr_sum = arry_sqr_sum + arry(i) * arry(i)
                BMA_arry_sqr_sum = BMA_arry_sqr_sum + arry_average(i) * arry_average(i)
            Next i
       
            RMS_arry = Sqr(arry_sqr_sum / items)
            RMS_BMA_arry = Sqr(BMA_arry_sqr_sum / items)
       
            If ((RMS_arry / RMS_BMA_arry) > 2) Then
                optimal = True
            Else
                optimal = False
            End If
   
            last_optimal_state = optimal
       
            Do
                For i = 1 To items
                    arry_average(i) = arry(i)
                Next i
           
                For j = 1 To iterations
                    arry_up(1) = arry_average(1)
               
                    For i = 2 To items
                        arry_up(i) = (arry_up(i - 1) + Exp(amplitude_degree) * arry_average(i)) / (1 + Exp(amplitude_degree))
                    Next i
               
                    arry_down(items) = arry_average(items)
               
                    For i = (items - 1) To 1 Step -1
                        arry_down(i) = (arry_down(i + 1) + Exp(amplitude_degree) * arry_average(i)) / (1 + Exp(amplitude_degree))
                    Next i
               
                    For i = 1 To items
                        arry_average(i) = (arry_up(i) + arry_down(i)) / 2
                    Next i
                Next j
   
                arry_sqr_sum = 0
                BMA_arry_sqr_sum = 0
                RMS_arry = 0
                RMS_BMA_arry = 0
           
                For i = 1 To items
                    arry_sqr_sum = arry_sqr_sum + arry(i) * arry(i)
                    BMA_arry_sqr_sum = BMA_arry_sqr_sum + arry_average(i) * arry_average(i)
                Next i
           
                RMS_arry = Sqr(arry_sqr_sum / items)
                RMS_BMA_arry = Sqr(BMA_arry_sqr_sum / items)
           
                If ((RMS_arry / RMS_BMA_arry) > 2) Then
                    optimal = True
                Else
                    optimal = False
                End If
   
                If (Not optimal) And (Not last_optimal_state) Then
                    amplitude_degree = amplitude_degree - amplitude_degree_precision
                ElseIf (Not optimal) And (last_optimal_state) Then
                    amplitude_degree = amplitude_degree - amplitude_degree_precision
                    amplitude_degree_precision = amplitude_degree_precision / 10
                ElseIf (optimal) And (last_optimal_state) Then
                    amplitude_degree = amplitude_degree + amplitude_degree_precision
                ElseIf (optimal) And (Not last_optimal_state) Then
                    amplitude_degree = amplitude_degree + amplitude_degree_precision
                    amplitude_degree_precision = amplitude_degree_precision / 10
                End If
       
                If (optimal) And (amplitude_degree_precision <= degree_precision) Then optimal_found = True
                If (Abs(amplitude_degree) >= 100) Then optimal_found = True
       
                last_optimal_state = optimal
   
            Loop Until optimal_found
   
            frequency_degree = 0
            frequency_degree_precision = 1
            optimal_found = False
   
            For i = 1 To items
                arry_average(i) = arry(i)
            Next i
       
            For j = 1 To iterations
                arry_up(1) = arry_average(1)
           
                For i = 2 To items
                    arry_up(i) = (arry_up(i - 1) + Exp(frequency_degree) * arry_average(i)) / (1 + Exp(frequency_degree))
                Next i
           
                arry_down(items) = arry_average(items)
           
                For i = (items - 1) To 1 Step -1
                    arry_down(i) = (arry_down(i + 1) + Exp(frequency_degree) * arry_average(i)) / (1 + Exp(frequency_degree))
                Next i
           
                For i = 1 To items
                    arry_average(i) = (arry_up(i) + arry_down(i)) / 2
                Next i
            Next j
   
            frequency_set(1) = 0
            frequency_set(2) = 0
            frequency_set(3) = 0
       
            For i = 1 To items
                If (arry_average(i) > 0) Then
                    frequency(i) = 1
                Else
                    frequency(i) = 0
                End If
            Next i
   
            For i = 1 To items - 1
                If ((arry_average(i + 1) - arry_average(i)) > 0) Then
                    diff_frequency(i) = 1
                Else
                    diff_frequency(i) = 0
                End If
            Next i
   
            For i = 1 To items - 2
                If ((arry_average(i + 2) - 2 * arry_average(i + 1) + arry_average(i)) > 0) Then
                    diff_diff_frequency(i) = 1
                Else
                    diff_diff_frequency(i) = 0
                End If
            Next i
   
            For i = 1 To items - 1
                frequency_set(1) = frequency_set(1) + Abs(frequency(i + 1) - frequency(i))
            Next i
   
            For i = 1 To items - 2
                frequency_set(2) = frequency_set(2) + Abs(diff_frequency(i + 1) - diff_frequency(i))
            Next i
   
            For i = 1 To items - 3
                frequency_set(3) = frequency_set(3) + Abs(diff_diff_frequency(i + 1) - diff_diff_frequency(i))
            Next i
   
            mn = frequency_set(1)
            mx = frequency_set(1)
            If frequency_set(2) < mn Then mn = frequency_set(2)
            If frequency_set(2) > mx Then mx = frequency_set(2)
            If frequency_set(3) < mn Then mn = frequency_set(3)
            If frequency_set(3) > mx Then mx = frequency_set(3)
   
            If ((mx - mn) > 3) Then
                optimal = False
            Else
                optimal = True
            End If
   
            last_optimal_state = optimal
       
            Do
       
                For i = 1 To items
                    arry_average(i) = arry(i)
                Next i
           
                For j = 1 To iterations
                    arry_up(1) = arry_average(1)
               
                    For i = 2 To items
                        arry_up(i) = (arry_up(i - 1) + Exp(frequency_degree) * arry_average(i)) / (1 + Exp(frequency_degree))
                    Next i
               
                    arry_down(items) = arry_average(items)
               
                    For i = (items - 1) To 1 Step -1
                        arry_down(i) = (arry_down(i + 1) + Exp(frequency_degree) * arry_average(i)) / (1 + Exp(frequency_degree))
                    Next i
               
                    For i = 1 To items
                        arry_average(i) = (arry_up(i) + arry_down(i)) / 2
                    Next i
                Next j
   
                frequency_set(1) = 0
                frequency_set(2) = 0
                frequency_set(3) = 0
           
                For i = 1 To items
                    If (arry_average(i) > 0) Then
                        frequency(i) = 1
                    Else
                        frequency(i) = 0
                    End If
                Next i
   
                For i = 1 To items - 1
                    If ((arry_average(i + 1) - arry_average(i)) > 0) Then
                        diff_frequency(i) = 1
                    Else
                        diff_frequency(i) = 0
                    End If
                Next i
   
                For i = 1 To items - 2
                    If ((arry_average(i + 2) - 2 * arry_average(i + 1) + arry_average(i)) > 0) Then
                        diff_diff_frequency(i) = 1
                    Else
                        diff_diff_frequency(i) = 0
                    End If
                Next i
   
                For i = 1 To items - 1
                    frequency_set(1) = frequency_set(1) + Abs(frequency(i + 1) - frequency(i))
                Next i
   
                For i = 1 To items - 2
                    frequency_set(2) = frequency_set(2) + Abs(diff_frequency(i + 1) - diff_frequency(i))
                Next i
   
                For i = 1 To items - 3
                    frequency_set(3) = frequency_set(3) + Abs(diff_diff_frequency(i + 1) - diff_diff_frequency(i))
                Next i
   
                mn = frequency_set(1)
                mx = frequency_set(1)
                If frequency_set(2) < mn Then mn = frequency_set(2)
                If frequency_set(2) > mx Then mx = frequency_set(2)
                If frequency_set(3) < mn Then mn = frequency_set(3)
                If frequency_set(3) > mx Then mx = frequency_set(3)
   
                If ((mx - mn) > 3) Then
                    optimal = False
                Else
                    optimal = True
                End If
           
                If (Not optimal) And (Not last_optimal_state) Then
                    frequency_degree = frequency_degree - frequency_degree_precision
                ElseIf (Not optimal) And (last_optimal_state) Then
                    frequency_degree = frequency_degree - frequency_degree_precision
                    frequency_degree_precision = frequency_degree_precision / 10
                ElseIf (optimal) And (Not last_optimal_state) Then
                    frequency_degree = frequency_degree + frequency_degree_precision
                    frequency_degree_precision = frequency_degree_precision / 10
                ElseIf (optimal) And (last_optimal_state) Then
                    frequency_degree = frequency_degree + frequency_degree_precision
                End If
           
                If (optimal) And (frequency_degree_precision <= degree_precision) Then optimal_found = True
                If (Abs(frequency_degree) >= 100) Then optimal_found = True
       
                last_optimal_state = optimal
       
            Loop Until optimal_found
   
            degree = (amplitude_degree + frequency_degree) / 2
   
            For i = 1 To items
                arry_average(i) = arry(i)
            Next i
       
            For j = 1 To iterations
                arry_up(1) = arry_average(1)
           
                For i = 2 To items
                    arry_up(i) = (arry_up(i - 1) + Exp(degree) * arry_average(i)) / (1 + Exp(degree))
                Next i
           
                arry_down(items) = arry_average(items)
           
                For i = (items - 1) To 1 Step -1
                    arry_down(i) = (arry_down(i + 1) + Exp(degree) * arry_average(i)) / (1 + Exp(degree))
                Next i
           
                For i = 1 To items
                    arry_average(i) = (arry_up(i) + arry_down(i)) / 2
                Next i
            Next j
   
            For i = 1 To items
                matrix(i, k + 1) = arry_average(i)
                arry(i) = arry(i) - matrix(i, k + 1)
            Next i
       
        End If
   
    Next k
   
    For i = 1 To items
        matrix(i, waves + 2) = arry(i)
    Next i
   
    TheWaveMatrix = matrix()
   
    Exit Function
   
ExitFunction:

    If Er(0) = "Error" Then Er(0) = " Error - " & err.Number & ", " & err.Description

    TheWaveMatrix = Er()
   
End Function