Calculating Weekly Average

P

Peter

I use Excel 2002. I will be entering data every day in a spreadsheet,
recording both the date and the data for that day. Is there any way for
Excel to automatically calculate the average for each week? In other words,
can I set up Excel to know that the week starts on Sunday and ends on
Saturday and average the data accordingly?

Thanks.
 
F

Frank Kabel

Hi
you could add a helper column showing the weeknumber and calculating
with this. or use a pivot table and goup by weeks
 
P

Peter

Thanks for the quick reply, but I'm a little confused. I've never created a
"helper column". Would I need to indicate the week number for every entry,
or would I simply insert a week number at the start of each week? In
addition, what sort of formula would I use for the cell that calculates the
average?

Thanks again,

Peter
 
F

Frank Kabel

Hi
o.k lets assume the following:
- column A: your dates
- column B: your values
- column C: your dedicated helper column

Enter the following in C1:
=WEEKNUM(A1)
and copy this down for all rows.
Note:
- the Analysis Toolpak Addin has to be installed for this function
- also for different weeknumber definitions see:
http://www.cpearson.com/excel/weeknum.htm

Now use the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(C1:C1000=2,B1:B1000))

This calculates the average for week 2
 
Top