Calculating Average ...of a dynamic data.

Realistically you would want to use VBA for this ... formula wise you would need to enable Iteration (to permit circular references) and these are Volatile, ie will recalculate whenever excel recalculates be it the result of the value updating or something else so this method is open to error.

In VBA terms, if we assume the cell being altered is A1, the running total to be stored in B1, the count of alterations in C1 then the below VBA might work for you:

Please Login or Register  to view this content.

To insert the above, right click on the Tab against which the code is to be applied - select View Code and paste above into resulting window, thereafter ensure Macros are Enabled.

Your average calc is thus B1/C1

How is A1 updated and how quickly is it altering ... you may have issues keeping track if the changes are too frequent etc... most likely you will need to revert to a Worksheet_Calculate event (rather than Change event)

I am unlikely to be online over the next few days but I will endeavour to resolve...

Hmm... I confess I have zero experience using DDE's but you would need to adopt a different event as already outlined given DDE udpates do not invoke a Change event, rather you need to use the Calculate event, however, the problem here is that technically you're open to error if the sheet recalculates on the basis of some action other than DDE update having taken place... if this is a reality we should discuss further, if not, that is to say only DDE's will be recalculating then perhaps try:

Please Login or Register  to view this content.

That said, given the above to all intents & purposes becomes a Volatile Event you could just as easily dispense with VBA, enable Iteration with Max Iteration of 1 (to permit circular references) and enter formulae instead:

B1: =B1+A1
C1: =C1+1

Just tried your latest VBA code , its now working with the DDE update , the only problem now is B1 is showing zero (basically not summing up A1 ) but C1 is counting fine .

That's because I'm a complete doofus..., should be:

Please Login or Register  to view this content.

Re: learning VBA - self taught I'm afraid... by self I mean: Macro Recorder, Forums like this one... there are a good few books about - Walkenbach always get good reviews, PED for the more advanced programmer...