1. When you get past 256 entries, you'll run out of columns, so you really
ought to use column A for dates, column B for values, and column C for the
calculated averages.

2. I made an example with dates in column A, starting in A4 (leaving some
room for headers and other information at the top). I put values in column
B, starting in B4. I array-entered the following formula in C4, then copied
it down as far as I had dates and values in columns A and B. To array enter
a formula, type or paste the formula, then hold Ctrl+Shift while pressing
Enter. If done correctly, Excel places the formula within {curly braces}.

=SUM(($A$4:$A4<=$A4)*($A$4:$A4DATE(YEAR($A4)-1,MONTH($A4),DAY($A4)))*($B$4:$B4))/SUM(($A$4:$A4<=$A4)*($A$4:$A4DATE(YEAR($A4)-1,MONTH($A4),DAY($A4))))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
ups.com...
Hi, I keep a rough weekly diary on the development of a certain numeric
value. Now I've got a series of data entries in a chart as follows:

A1 - date of entry, eg. "23rd July 2006"
A2 - reading data value, eg "100"

I want to create a dynamic average on line A3 which tells me the
average of data entries during the past year. Problem is, I've read the
data value in irregular intervals and not every day (last year for
example on 22nd July, the year before 10th July etc). My Excel
understands the dates entered and draws a neat proportioned chart on
development of data values for the whole period of surveillance, but
I'd need to be able to see the effect of the latest reading on the
dynamic average of the past year only. Is there a smart way of doing
this?

(the way I don't want to do it: create a column for every single day in
calendar and always enter my data value in that column... that way I
could easily subtract the value of 365 days ago from present value, but
in 10 years I get a monster of a chart. There must be a better way!)