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.