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

Ruth

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

"Ruth" wrote in message
...
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
--
Thanks

Ruth

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" wrote:

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

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.
--
Thanks

Ruth


"Fred Smith" wrote:

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

"Ruth" wrote in message
...
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
--
Thanks

Ruth

.

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" wrote in message
...
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.
--
Thanks

Ruth


"Fred Smith" wrote:

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

"Ruth" wrote in message
...
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
--
Thanks

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.
--
Thanks

Ruth


"Fred Smith" wrote:

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" wrote in message
...
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.
--
Thanks

Ruth


"Fred Smith" wrote:

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

"Ruth" wrote in message
...
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
--
Thanks

Ruth

.


.

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

--
Regards
Roger Govier

Ruth wrote:
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.

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" wrote in message
...
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.
--
Thanks

Ruth


"Fred Smith" wrote:

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" wrote in message
...
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.
--
Thanks

Ruth


"Fred Smith" wrote:

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

"Ruth" wrote in message
...
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
--
Thanks

Ruth

.


.

That works perfectly has made my life much easier

Thank you
--
Thanks

Ruth


"Fred Smith" wrote:

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" wrote in message
...
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.
--
Thanks

Ruth


"Fred Smith" wrote:

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" wrote in message
...
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.
--
Thanks

Ruth


"Fred Smith" wrote:

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

"Ruth" wrote in message
...
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
--
Thanks

Ruth

.


.


.

Glad I could help. Thanks for the feedback.

Fred

"Ruth" wrote in message
...
That works perfectly has made my life much easier

Thank you
--
Thanks

Ruth


"Fred Smith" wrote:

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" wrote in message
...
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.
--
Thanks

Ruth


"Fred Smith" wrote:

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" wrote in message
...
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.
--
Thanks

Ruth


"Fred Smith" wrote:

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

"Ruth" wrote in message
...
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
--
Thanks

Ruth

.


.


.

Hi Fred,

I would also like to thank you for your solution, because this was also my question and it workd great.
I only would like to add the average of 1 year back over the same period. So I would like to average the data over the last 6 weeks and the same six weeks, but 1 year ago. My data looks kind of the same as Ruth's.

Hopes this questions is clear.

Thanks in advance,

Nynke