Dynamic Average or Rolling Average

[davey11372]

I have a cell that has an underlying average formula. The data that feeds the formula is dynamic, via a DDE link. In other words, the data is not converted to values and stored/tabulated anywhere on the sheet.

I want to calculate a 9 value running average of the data fed via the live DDE link. To try an make myself clearer -

If I have data tabulated in column A - I can calulate a 9 value average of the first nine cells by = Sum(A1:A9)/9 (or AVG(A1:A9))

In this situation - only cell A1 has data being fed to it, the data populates the cell and gets updated dynamically and is replaced by the new updated data.

I want to calculate the running average of every 9 values as the data enters and leaves the cell (so as new data is updated to the cell, one value gets dropped as the new value is populated and new average calculated) - if you are familiar with stocks - kind of running moving average for a 9 period set.

Is this doable without tabulating/caching the data on the sheet?

Thank you

 

[xenou]

Are you using a worksheet calculate event to trigger this? Keeping the 9 values is relatively trivial - I would not rule out saving them on the worksheet (personally, I'd prefer that, so if you like you can see the 9 values that make up the average):

1) store them on a worksheet in some convenient place (hiding columns, using white font, invisible formatting, or a hidden worksheet will keep them out of sight - I use white font a lot, because "selecting" over the range allows me to see their "ghostly" presence if I want to know what's there, but otherwise is clean and tidy for presentation.)

2) use a persistent variable to store the values in memory - along with a counter to track the last saved value. Your calculate event will trigger updating the counter and the last variable, and of course recalculate the average.

 

[davey11372]

Thanks for the reply. I will try to incorporate worksheet calculate event .. never tried it before.

One change to my original post/question - the cell value of the cell in question is the difference of two other cells (C5-C7)(and not via a DDE feed, the DDE feeds another cell in the worksheet, all calculations are ultimately off the DDE data, so the cell in question does not directly update via DDE).

Sorry for not being precise in my first post.

If this makes it easier for alternate/additional solutions - would appreciate it.

Thanks.

 

[xenou]

It seems to me you'll have to use a calculate event still to capture the changing data without losing the previous eight values. There's lots of examples here on the board and elsewhere - let me know if you run into trouble. Right clicking the sheet tab and choosing view code will drop you into the code view for the sheet where you're cell value is changing - you want to capture sheet calculate events on that sheet.

With XL 2007 you need to save the file as an .xlsm and put it in a "trusted location" (which must be set under options). With XL2003 you might set macro security to Medium (also under options - security - macro security).

 

[davey11372]

when I tried to view code, it is asking me for a password. Can I create a macro to run on a sheet that does not allow be to insert a VBA module or change the code? If so, how?

 

[xenou]

That's very strange. Is it possible someone else has already written code in this workbook? I would have expected no code yet (if this is your workbook and you yourself have written no code in it before) - or rather, there should be no passwords except those you have set yourself. I'll give my standard advice - move everything into a clean workbook and start fresh!

Rebuild Your Workbook

 

[xenou]

Edit: Note that you may like to test your access to the vba project by simply opening a new workbook and trying there - that is, to be sure this is a problem unique to your workbook rather than with Excel generally on your machine.

ξ

 

[davey11372]

Ok, thanks.