Glenn

Thank you for your reply.

When I use this formula, it does not seem to take account of the actual date
values.

I think I may have explained my problem badly so by way of clarification:

I record data on a regular basis but not every day. What I want to be able
to do is to average the values recorded for the last 30 days.

Obviously, if I had a value in for every sequential day, just averaging the
last 30 rows would be easy. The problem is that the number of rows to be
averaged keeps changing depending on how many days in the last 30 have a
value recorded against them so I need a way of calculating how many rows of
data should be averaged.

Sorry if my original question was misleading. I would be grateful for any
help you can give.

Many thanks


"Glenn" wrote:

Zorro wrote:
I would like to find a formula that allows me to sum or average numbers from
a column of data over a number of days to be entered in a separate cell.

The data consists of a "date" column with not all days included and an
associated column of values for example:

A B C D
Date Value Rolling average Days for average
1/12/2008 150 30
10/12/2008 183
1/1/2009 100
1/5/2009 200
1/6/2009 130
1/10/2009 150

What I would like to be able to do this to calculate an ongoing average for
each row based on a value (e.g. 30 as shown in column D of the example above)
entered in another cell.

I have tried to do this using the sumif function but, although it works if I
use an absolute number in the criteria field, as soon as I try to reference a
value contained in a cell it returns the value of zero.

I would be very grateful for any help with this.

Many thanks


For SUM:

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

For Average:

=SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2), $B$2:$B$99)/SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2))