Yeahh

this weekend. Now, on to the garage and porch.

I'm Busier Than A Beaver At A Log Jam Forest Festival.

Was for Few weeks in Omaha, then over to Chicago land for a week.

Then, Back on my Roof over the weekend in Holy Hell of Heat.

Down to Waterloo then to my second home in Oelwien to mow the lawn.

Back up to Owatonna, then going to St. Cloud tomorrow.

Making a trip to Des Moines Wednesday and back up to Owatonna Thursday...

then I get to do more work on my home in MN.

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

Eh, a little more tan... or maybe just the lighting.

I'll get some more in a few weeks.

Before

After

Oo, that was fun!!

Reroofing my MN home myself; with my son's help.

Doing it in three stages, front then two new addition sides.

Got a little toasted yesterday, nice crispy brown.

Good thing I have Injun skin... browning nicely.

Got the front done.

Now, off to Omaha on an install for two weeks.

"The message was not delivered because the recipient's inbox is full."

Anyone sending me a Private Message needs to clean their mail box.

I cannot reply when your mail box is Full.

The Wave Matrix

Below is the basic function setup and parameters:

=TheWaveMatrix(Range, Waves, Iterations, Precision)

Range - Selected rows of data

Waves - Number of optimized waves to derive through the Bidirectional Mean Averaging algorithm
{1, 2, 3, ...}

Iterations - Number of repeated passes through the Bidirectional Mean Averaging algorithm
{1, 2, 3, ...}

Precision - Number of decimal places the Degree of Bidirectional Mean Averaging will be optimized
{1, 2, 3, 4, 5, 6, 7, 8} = {0.1, 0.01, 0.001, 0.0001, 0.00001, 0.000001, 0.0000001, 0.00000001}

To get this setup in Excel, you'll have to setup a macro enabled workbook.

Create a new workbook

Open the Microsoft Visual Basic for Applications window by pressing ALT + F11

Use the Insert menu to add a Module or use the second icon drop down in the upper left to add a Module

Next, Select and copy only the function code we posted previously and paste it in to the Module code section

Now, Save the workbook using the "Save As..." and make sure to use the "Save as type" selection to save as an Excel Macro-Enabled Workbook (*.xlsm)

Close the file and reopen it, Excel will ask if you want to Enable Macros in the top bar by pressing Enable Content

Once enabled, you can add some data and analyze with The Wave Matrix function

Start typing the function next to the top row of Data in the column to the right, "=the" will prompt an auto fill with the correct function, press tab to auto fill "=TheWaveMatrix("

Next, use the arrow keys to select the top row of the single column of data to analyze, press and hold the shift key, then select all the way down to the last row of data

Now for something simple to start with, press the "," to enter the number of waves; start with 4

Press "," to enter the number of iterations; use 4

Finally, press "," to enter the precision; try 4

Press enter to input the function

Depending on the amount of data, number of waves, iterations and precision, this could take a bit to evaluate

Once done, the cell you typed the "=TheWaveMatrix" function should have a data point or an error

If an error, recheck your entry

If data, get ready to populate the sheet with the Regression, Waves and Remainder

Select the cell with TheWaveMatrix function you just typed in

The number of waves will determine how many columns over from that newly populated cell you'll be moving to the right as we select and highlight all the cells for TheWaveMatrix

Press and hold the shift key

Using the arrow key move right 5 cells so you have a total of 6 columns selected (1 Regression + 4 Waves + 1 Remainder = 6 Columns)

Now, while still holding the shift key, move down to the same number of rows the Data has

Release the shift key

Press the F2 key, then press CTRL + SHIFT + ENTER together

Tada, you should now have the select cells populated with the wave matrix data

We'll post some screen shots in another post.