How do I set Up Dates - WEEKLY?

[rkrieg]

I'm trying to do the following:

a) Have one column show the date of each Saturday (starting 1/18/09) in each row, for example, 1/18/09, then 1/25/09, then 2/1/09, etc.

b) Have each other column show the corresponding averages for the week prior(referencing data in another sheet)--how do I set it up to only calculate the average for each week in each row, for example, average(B1:B7), then next row =average(B8:B15).


Thanks for your help guys!

[rkrieg]

FYI: I'm running Office 2008 for Mac.

[Special-K]

This will work, type it into cell A1 and copy down column A.
Every 7 cells from row 1 the average will be calculated else blanks will appear

=IF(INT((ROW(A1)+6)/7)*7-6=ROW(A1),AVERAGE(B1:B7),"")

[rkrieg]

That's perfect for the formula, but is there any way to do it so it enters on each line instead of skipping 6 lines in between? What about making excel automatically generate the week ending in column? I'd like it to look like this:

Column A Column B
1/18/09 | =average over that week
1/25/09 | =average over that week
2/1/09 | =average over that week


Thanks!

[Special-K]

Copy down column A

=AVERAGE(INDIRECT("B"&INT((ROW(A1)+6)/7)*7-6&":B"&INT((ROW(A1)+6)/7)*7))