Zorro wrote:
Don

Thank you for your reply and apologies for any confusion I may have caused.

In answer to your questions, I meant calendar days and cell D2 contains the
number of days over which I want to calculate the sum or average so that, for
example, by putting 30 in that cell I would calculate a 30 day average and by
putting 7 in the cell I would calculate a seven day average.

I am setting out below an example of some real data to which I would like to
apply this. In the period 12 October, 2008 to 1 December, 2008 I have
calculated a 30 day rolling average manually to show the results that I would
expect. I have not done so for the other numbers. To explain this further:

the value of 320 showed against 12 October is the average of all the values
going back to 29 September which is the first value and is less than 30 days
before 12 October

The value of 294 against December 1 is the average of the four values going
back to 9 November which is the earliest date within 30 days before 1 December

Once again my apologies for any confusion I may have caused and I would be
very grateful for any help you can give.

Many thanks

Date Value Rolling Average Days
29 September 2008 200 30
02 October 2008 207
05 October 2008 480
08 October 2008 221
10 October 2008 398
12 October 2008 416 320
09 November 2008 254 335
10 November 2008 509 393
30 November 2008 207 323
01 December 2008 207 294
02 December 2008 207
14 December 2008 505
16 December 2008 590
20 December 2008 650
22 December 2008 650
24 December 2008 645
04 January 2009 484
11 January 2009 487
12 January 2009 513
15 January 2009 502




Put this in C2 and copy down as needed. Adjust the 100's to include all of your
data.

=SUMPRODUCT(($A$2:$A$100<=A2)*($A$2:$A$100A2-$D$2),$B$2:$B$100)/SUMPRODUCT(($A$2:$A$100<=A2)*($A$2:$A$100A2-$D$2))