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:

Pingback: links for 2009-04-26 | dekay.org

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!

LikeLike

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.

LikeLike

Hello:

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.

LikeLike

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?

tnks

LikeLike

This is a long shot but just in case you see this comment and might be able to help, here is my question. I have a spreadsheet in which I have a single column of numerical data. I’ve defined a name for that range (AvData). The function works well on the workbook when array entered and displays without any problems. However, If I instead define a name MAvg5 as referrring to

movav(AvData,5), entering =MAvg5 in a cell results in an error message #VALUE. Do you have any idea what could be going on?

LikeLike

That seems to work OK for me. If you would like to send your spreadsheet to dougaj4 at my gmail account, I will have a look.

LikeLike

Thanks very much for your reply. I’ve emailed you the spreadsheet with what I hope will illustrate the issue I’m having. Thanks again for your willingness to help.

LikeLike