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" <BarrySandell@discussions.microsoft.com> wrote in message
news:AB17C5B4-496E-46D5-8730-9359FE77B332@microsoft.com...
>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
<BarrySandell@discussions.microsoft.com> 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" <BarrySandell@discussions.microsoft.com> wrote in message
news:7AF734CE-47FB-43D6-8594-A9D68FE299DE@microsoft.com...
> 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?