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

.


.


.