# Dynamic Average or Rolling Average

#### davey11372

##### Board Regular
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

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### xenou

##### MrExcel MVP
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
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
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
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
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!

#### xenou

##### MrExcel MVP
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.

ξ

Ok, thanks.

Replies
0
Views
188
Replies
15
Views
331
Replies
6
Views
610
Replies
2
Views
202
Replies
0
Views
138

1,186,179
Messages
5,956,400
Members
438,249
Latest member
georgebasalic3

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back