Average of last 30 days

[Ruth]

Hi
I update a spreadsheet daily with my forecasted calls vs actual calls, I
would like to keep a rolling average for the 7 days of the week that looks
back over the last 30 days of information. So it would be set out as below:

Day Average
Saturday ##
Sunday ##
Monday ##
Tuesday ##
Wednesday ##
Thursday ##
Friday ##

Any help would be greatly appreciated

 

[Fred Smith]

You need to provide more information -- where is the data (how is it laid
out), and what form are your dates in? Are they true Excel dates, or the
words "Saturday", etc.

Regards,
Fred

 

[Mike H]

Ruth,

I'm still confused. You want an average of the last 7 days so why do we
look back 30 days? Let's assume your data are in column A starting in A2.
isn't it as simple as putting this in B8 and dragging down each day to get
the last 7 days

=AVERAGE(A2:A8)

so when you enter data in A9 and drag the formula down it becomes

=AVERAGE(A3:A9)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Ruth]

I have data going back to April last year which i keep adding to, I want so
highlight the last 30 days so it will help me look at what we are forecasted
going forward.
My information is currently set up as below:

A B C
Weekday Date Call Volume
Saturday 01/03/2010 1000
Sunday 02/03/2010 1050
Monday 03/03/2010 2000
Tuesday etc etc
Wednesday etc etc
Thursday
Friday
etc
etc

The days of the week are just text and I would like a seperate sheet to be
laid out as below showing me the average:

A B
Day Of Week Average Call Volume from last 30 days
Saturday 1050
Sunday 1000
Monday 800
Tuesday etc
Wednesday etc
Thursday
Friday

Hope this explains the situation a little better.

 

[Fred Smith]

It helps, but doesn't tell us where the data is. If you want to average each
day of the week over the last 30 days, then for most days, you're going to
average four days, right? Is there any magic behind the 30-day figure? What
about averaging the last 28 days (4 weeks) -- that would make the
calculations a lot simpler.

Trying to get a handle on your data layout, if your last entry was for
23/03/2010 was on, say, row 844, then you would want the following for the
average of Tuesdays in the last four weeks:
=average(c844,c837,c830,c823)
Is that correct?

Regards,
Fred

 

[Ruth]

Averaging the last 28 days would be fine if it makes the calculation easier.

Yes to your other question the average would go
=average(c844,c837,c830,c823) then the numbers would role by 7 every weekto
give me the continual last 4 weeks.

I tried to set up a formula that looked at todays date using =now() and - 30
days but I couldnt get it to work I might have been barking up the wrong tree
anyway.

 

[Roger Govier]

Hi Ruth

I think the following formula will do what you want.
=AVERAGE(INDEX(C:C,(ROW(A1)-1)*7+823),INDEX(C:C,(ROW(A1)-1)*7+830),
INDEX(C:C,(ROW(A1)-1)*7+837),INDEX(C:C,(ROW(A1)-1)*7+844))

Copy down, and it will step up by 7 rows each time

 

[Fred Smith]

Here's my solution:

=sumproduct((sheet1!a1:a10000="Saturday")*(sheet1!b1:b10000>=today()-28)*(sheet1!c1:c10000))/4

It's not that elegant, but it should work. Adjust the ranges to suit. It
assumes that column A really is the day of the week in text (not, for
example, a date formatted as dddd), and that column B has true Excel dates.

If you make the ranges absolute, and change "Friday" to the cell which
contains this text, you should be able to copy this formula down for the
week.

Regards,
Fred

 

[Ruth]

That works perfectly has made my life much easier

Thank you

 

[Fred Smith]

Glad I could help. Thanks for the feedback.

Fred