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