Dynamic Average or Rolling Average

davey11372

Board Regular
Joined
Dec 19, 2009
Messages
56
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
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

Board Regular
Joined
Dec 19, 2009
Messages
56
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
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

Board Regular
Joined
Dec 19, 2009
Messages
56
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
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.

ξ
 

Forum statistics

Threads
1,081,560
Messages
5,359,604
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top