Skip x cells and fill (Fill/create weekly average from 7 days and fill down)

[tunafishes]

I don't know why I can't get this right, I googled and tried a bunch of suggestions that matched my problem but I'm going wrong somewhere.
Simply put I want to fill down in a seperate column some averages skipping every 7 cells. It has labels of 4/7, 4/14, 4/21, down each row and next to it I would like to fill in weekly averages like =average(A1:A7), =average(A8:A14), =average(A15:A21)
Naturally fill down will give me =average(A1:A7), =avg(A2:A8), A3:A9, etc which is no good to me.
I thought OFFSET would do it for me but I must be doing something wrong.


heres a snippet of my data (ignore the % column)

I know I have to be missing something real simple here---in fact I think I used to know how to do this but after messing around with pivot tables and failing at offset I figured I better just get help. Thanks to any assistance anyone can offer.

[Tony Valko]

Try this...

Entered in C1 and copied down:

=AVERAGE(OFFSET(A$1,ROWS(C$1:C1)*7-7,,7))

[tunafishes]

Thanks. Thought it didn't work at first because I transposed the ROWS array as C$1:C7 to mine but after I minute I caught it and it worked like a charm. Thanks for ending my frustration with this, have alot of worksheets that will be using this going forward so I really appreciate your reply.


Thanks again!

[Tony Valko]

You're welcome. Thanks for the feedback!

[harryxmin]

Capture.PNG
Capture1.PNG

Can anyone help me? i want the vertical yellow column (quantity) to show the data from balance. The vertical data is obtained using index to show the horizontal balance data. The balance figures are updated row by row. I want the quantity to update automatically as we add new balance figures horizontally.