Weekly average from columns?

[RyanF]

Evening,

I know this is a common question but I've done some google and Mr Excel searches on this but there's so many answers I can't find one I understand enough to apply to my circumstances

Here is some sample data, the row and column numbers are the ones I'm using

	A	B	C	D	E	F	G	H	I	J	all the way to ST
5	Date	1/1/13	2/1/13	3/1/13	4/1/13	5/1/13	6/1/13	7/1/13	8/1/13	9/1/13	(these are dd/mm/yyyy)
6	Variable 1	x	x	x	x	x	x	x	x	x
7	Variable 2	x	x	x	x	x	x	x	x	x
8	Variable 3	x	x	x	x	x	x	x	x	x
9	etc down to row 44	x	x	x	x	x	x	x	x	x

<TBODY>
</TBODY>

I want to calculate the average for each week (every 7 columns) and have the results collected together. The only way I can think to do this is a very manual one... I know there's a faster way, I just don't understand MOD or INDIRECT or the resoning behind the other solutions I've found.

I'm after an output like this:

	A	B	C	D	E	F	G	H	I	J	all the way to ST
46	Week commencing	1/1/13	7/1/13	14/1/13	21/1/13	28/1/13	4/2/13	etc	etc	etc	(these are dd/mm/yyyy)
47	Variable 1	av.	av.	av.	av.	av.	av.	av.	av.	av.
48	Variable 2	av.	av.	av.	av.	av.	av.	av.	av.	av.
49	Variable 3	av.	av.	av.	av.	av.	av.	av.	av.	av.
50	etc	av.	av.	av.	av.	av.	av.	av.	av.	av.

<TBODY>
</TBODY>

The source table is fully populated with numbers, and I want to consider 0's in the average (though I know enough that I could change a formula to ignore zeroes)

 

[norulen]

Do these steps probably it ll give you a solution to what you are looking for: I ll explain the procedure for Variable 1 hope you ll be able to adapt for other variables
Step 1: Insert one row below the date row for week number with this formula"=WEEKNUM(B1,2)"
2 here represents the week starting from monday you may change it if required
Step 2: Paste this formula for variable 1 "=SUMIF(2:2,WEEKNUM(B8,2),3:3)/(C8-B8)"
2:2 refers to row number in which week number has been iserted
B8 refers to week commencing date (in your example 1/1/13)
C8 refers to the week ending date (in your example 7/1/13)

Change to date format if not working

 

[RyanF]

Thanks for the possible solution norulen.

I managed to solve the problem myself using the OFFSET function, and COLUMN indexes to average every 7 columns. It's not as rigorous as yours but it got the job done :/

In the case of Row 5 I used the following formula starting from the second average (in my example C47) and filled it across and down.
=AVERAGE(OFFSET(C6,,(COLUMN(C6)-2)*7-(COLUMN(C6)-2),,7))

 

[norulen]

Thats a good one Thanks for posting your solution on the forum as well I am sure it ll help someone some day