Glenn

Thank you for this. I think we are nearly there.

The formula that you have given me seems to contain a typo in that it
references cell E2. When I correct that to D2 it does add up a 30 day range
of values but it does so going forward rather than backwards so that, for
example, if you have a range of values recorded between 1 December and 25
December, the entry against 1 December will add up all of values between 1
December and 25 December whereas the value against 25 December will just be
that value assuming that 25 December is the last value recorded.

I would be grateful if you can tell me how I should modify the formula so
that it calculates the other way round.

Once again many thanks for your help: it really is very much appreciated.

"Glenn" wrote:

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))


Sorry, I inserted a column for Average (and deleted a row) between copying these
formulas. Assuming Date, Value, Sum, Average and Days in A:E (and only a single
value in E2 for Days), the correct formulas, which can be copied down the
columns, should be as follows:

=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), $B$2:$B$99)/SUMPRODUCT(($A$2:$A$99=A2)*($A$2:$A$99<A2+$E$2))