Comment #1

JADELottery — Thu, 08 May 2025 13:39:54 GMT

I'll post a "How To..." either tonight or tomorrow.<br />Keep in mind, I will not be running an Excel 101 Class.<br />I'll expect you to know how to use Excel reasonably well and how to navigate the spreadsheet and using functions therein.

Original Blog Entry: The Wave Matrix Module Function Code

JADELottery — Wed, 07 May 2025 03:02:41 GMT

It's late...

I'll be traveling to IA early tomorrow.

Below is the code and a link to a text file of the code.

I will get more in to it later.

For now, you can examine.

If you know what to do, do it.

https://member.lotterypost.com/jadelottery/images/TheWaveMatrixModuleFunctionCode-00.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 As Long: items = theRange.Rows.Count

Dim arry() As Double: ReDim arry(1 To items): For i = 1 To items: arry(i) = theRange.Cells(i, 1): Next i

Dim matrix() As Variant: ReDim matrix(1 To items, 1 To (waves + 2))

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

If (theRange.Columns.Count 1) Then Er(0) = Too Many Columns Selected : 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

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... [ More ]