Moving averages and User Defined Array Functions

I have recently needed to work with moving averages on a large-ish data set (about 10,000 rows x 10 columns), and for reasons that I will describe in the next post, decided that a User Defined Function (UDF) would be useful.  Writing the UDF was simple enough, but because of the large number of data transfers between the spreadsheet and VBA it was painfully slow.  Fortunately it is easy to write a UDF as an array function, which allows all the data to be transferred in one step, the processing to be carried out within VBA, then the results to be written back to the spreadsheet in one step.  This dramatically improves the performance; to near instantaneous in the case of my 100,000 cell data set.

The procedure in outline is:

  • Declare the input datarange(s) and the function itself as variants
  • Convert the input range from a variant/range object to an array of variants with the statement: DataRange = DataRange.value2
  • Dimension an array of the required size for the results:  Dim ResA(1 to numrows, 1 to numcols) as double
  • Perform the necessary calculations and assign the results to ResA.
  • Assign the results array to the function return value:  FunctionName = ResA
  • That’s all

If you enter the function in the spreadsheet in the usual way it will only display the contents of array position (1,1).  To display the full array:

  • Enter the function in one cell in the usual way
  • Select a range large enough to hold the whole array, with the entered function in the top-left corner.
  • Press the Edit key (F2)
  • Press Ctrl-Shift-Enter
  • The array values will be transferred to the selected range

Code for a moving average array function is shown below.  This has the following advantages over using the built-in Average() function, or the “moving average wizard” in the data analysis add-in:

  • The number of values to be averaged is specified as a variable, rather than requiring the formula to be re-entered.
  • It will handle any number of columns, up to the spreadsheet limit
  • It can be easily refined to provide additional functionality, for instance weighted moving averages, as will be described in the next post

Note that this is a simplified function, with no error checking, and blank cells treated as zero.

Function MovAv1(AvData As Variant, Steps As Long) As Variant
Dim MovAvA() As Variant, NumRows As Long, NumCols As Long
Dim i As Long, j As Long, MovSum As Double
AvData = AvData.Value2
NumRows = UBound(AvData)
NumCols = UBound(AvData, 2)
ReDim MovAvA(1 To NumRows, 1 To NumCols)
For j = 1 To NumCols
MovSum = 0
For i = 1 To Steps
MovSum = MovSum + AvData(i, j)
MovAvA(i, j) = CVErr(xlErrNA)
Next i
MovAvA(i - 1, j) = MovSum / Steps
For i = Steps + 1 To NumRows
MovSum = MovSum + (AvData(i, j) - AvData(i - Steps, j))
MovAvA(i, j) = (MovSum) / Steps
Next i
Next j
MovAv1 = MovAvA
End Function

Screen shot:

Moving Average Function

Moving Average Function

This entry was posted in Arrays, Excel, UDFs, VBA and tagged , , , , . Bookmark the permalink.

5 Responses to Moving averages and User Defined Array Functions

  1. Pingback: links for 2009-04-26 |

  2. geoffness says:

    Very nice, thanks for posting this Doug. I will be doing some time series analysis over the next couple of weeks which this will come in very handy for – it will save a lot of time being able to use one array function to cover all the averages I need to create. Normally I’d do this all in SAS but it’s a lot easier to create the data points in SAS and do the analysis in Excel. Cheers!


  3. dougaj4 says:

    Geoff – glad you found it useful, and I appreciate getting the feedback. I’ll be adding an option for weighted averages in the next few days, as well as ignoring text and blank cells, and also optionally ignoring zeros.

    BTW, I’ve just started looking into Python in my spare time (should any come along), so I’ll be looking at the Python on your site when I get into it.


  4. Manuel Sahagun Medina says:

    I am from Guadalajara, México and want to thank for all your interesting comments and files in this blog, specially this vba function.

    Greetings from México.


  5. gaia says:

    That’s really useful!!!! Btw I am trying to compute the Centered MA, that means traslated behind by half the number of steps, is there anyone that can help me?



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s