How to calculate moving averages with a complicated requirement

Hi,

I am very new to VBA and macro programming, but need urgently to calculate moving averages for a data series in a rather complicated manner. I hope someone can kindly offer advice to the problem below.

Suppose I have a hundred data points in cells A1 to A100, and two numbers in cell B1 and C1. Further suppose that these data are only determined at runtime.

I need to calculate moving averages based on the data points in A1 to A100, where the number in B1 determines the number of data points that goes into the calculation of each moving average figure, and the number in B2 determines the "gap" between the data points. Let me illustrate with an example.

Suppose B1 = 6 and B2 = 2, and I am calculating the moving averages to fill in the blanks D1 to D10.

Therefore D1 = the average of (A1 + A4 + A7 + A10 + A13 + A17), and D2 = the average of (A2 + A5 + A8 + A11 + A14 + A18) etc.

In this case, B1 = 6 determines the number of data points that is used to calculate each moving average (for the example 6 values from series A is used to calculate each value in series D). B2 =2 determines the "gap" between each data point i.e. the average for D1 is taken for A1, A4 (skipping 2 numbers A2 and A3), A7 (skipping 2 numbers A5 and A6) and so on.

If B1 = 4 and B2 = 1, then D1 = the average of (A1 + A3 + A5 + A7) and D2 = the average of (A2 + A4 + A6 + A8).

How can I go about doing this? Any VBA code examples will be most appreciated!

See attached file... I hope it can help you.

Regards,
Antonio

Hi,

Thank you so very much, it works! Awesome...

Hi Antonio,

Sorry, I still have some follow-up questions about this case. Briefly,

1. If I want Column D (in your sample) to refresh itself automatically every time the values in B1 and/or B2 are changed, i.e. without needing to click the Elaborate button, how do I set the .xls up to do so? In fact, I don't really need the button. I just need column D to output the numbers automatically.

2. In your sample, Column A comprises just the data and nothing else, therefore I notice you use Cells(Rows.Count, "a").End(xlUp).Row to count the number of data items in Column A. What if the first few rows of the worksheet begin with some non-numeric content e.g. typed passages and the exact number of data items is not known until run time? How can I change the code to take this into account?

For e.g. Assume the first 4 rows are text i.e. A1 to A4 are non-numeric and not intended for calculating the moving averages. Further suppose that the numeric data is filled in only from A7 onwards, and at run time 200 data are generated (from A7 to A206). How can I then generate the moving averages in Column D?

Thanks!