How can I calculate weekly totals of daily data in Excel
 

I have an Excel spreadsheet of daily totals where some days have no values
and some days have several values. I want to consolidate this into weekly
totals. Any ideas?

=AVERAGE(IF((A1:A30=J1)*(A1:A30<=K1),B1:B30))

entered with ctrl + shift & enter where K1 is the first date of the week and
K1 the last

--
Regards,

Peo Sjoblom

(No private emails please)


"BarrySandell" wrote in message
...
I have an Excel spreadsheet of daily totals where some days have no values
and some days have several values. I want to consolidate this into weekly
totals. Any ideas?

On Thu, 6 Oct 2005 16:01:03 -0700, BarrySandell
wrote:

I have an Excel spreadsheet of daily totals where some days have no values
and some days have several values. I want to consolidate this into weekly
totals. Any ideas?

How about a pivot table.

Drag the dates to the row area.
Drag the Values to the data area.

Right click on dates and select
Group and Show Detail/Group
Select a starting date that reflects the start of week 1 and then group by Days
with Number of Days set to 7


--ron

Thanks for your responses. I thought of a simple solution about 5 minutes
after posting my question. Typical!

Use the WEEKNUM function to convert each date into the number of the week in
which it appears, then use a pivot table to sum the values for each week. To
convert the weeknum back to a date (eg for plotting on a chart), add the week
number * 7 to the Excel index number for 1st Jan, then format the result as
d/mm/yy. Works every time!

"BarrySandell" wrote:

I have an Excel spreadsheet of daily totals where some days have no values
and some days have several values. I want to consolidate this into weekly
totals. Any ideas?

Just a heads up if you send the file(s) to someone else, WEEKNUM is part of
the Analysis ToolPak and many users in a corporate environment might not
have it installed (they'll get a name error)
--
Regards,

Peo Sjoblom

(No private emails please)


"BarrySandell" wrote in message
...
Thanks for your responses. I thought of a simple solution about 5 minutes
after posting my question. Typical!

Use the WEEKNUM function to convert each date into the number of the week
in
which it appears, then use a pivot table to sum the values for each week.
To
convert the weeknum back to a date (eg for plotting on a chart), add the
week
number * 7 to the Excel index number for 1st Jan, then format the result
as
d/mm/yy. Works every time!

"BarrySandell" wrote:

I have an Excel spreadsheet of daily totals where some days have no
values
and some days have several values. I want to consolidate this into weekly
totals. Any ideas?