How can I calculate weekly totals of daily data in Excel

[Guest]

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?

 

[Peo Sjoblom]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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!

 

[Peo Sjoblom]

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)