Running total over a find number of days

[Zorro]

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

 

[Don Guillett]

try this idea
AVERAGE(a2:OFFSET(a2,d2,0))

 

[Bob Phillips]

Is this what you mean?

=SUM(B:B)-SUMIF(A:A,">"&A2+D2,B:B)

 

[Zorro]

Don

Thank you for your reply. Using the formula that you have given me returns
the date on which value from the left-hand column.

I think I may not have explained what I am trying to do very well 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.

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

Many thanks

 

[Zorro]

Bob

Thank you for your reply.

When I use this formula, it seems to sum a defined number of rows but does
not 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]

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

 

[Zorro]

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]

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

 

[Don Guillett]

When you say the last 30 days do you mean 30 days or 30 calendar days? What
did cell d2 have to do with it?
perhaps a clearer explanation along with before/after examples would be
nice.

 

[Glenn]

Glenn

Thank you for your reply.

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

Did you actually try them (or the corrected versions I also posted), or just
decide they won't work based upon visual inspection?

If you did try them, please describe the actual data and results so we can get
to the bottom of the problem.

 

[Zorro]

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

 

[Zorro]

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]

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.

Corrected (again) in another reply.