is OFFSET the functcion for me?

[Guest]

Boy, I really oversimplified my example in a previous posting. Let me try
again.

I have daily data points. I want to summarize with weekly averages.
Column A has 365 rows of dates, increasing by 1 day with each row.
Column B has 365 rows of values.
Column C has 52 rows of dates, increasing by 7 days with each row.
I want column D to look at the date next to it in column C, and give me the
average value from column B for that date and the 6 days previous to it (the
weekly average).

I'd like to know if there's a way to write a function in the first row of
column D so that I can copy it to the cells beneath it and not have to change
the arguments in each row.

Thank you.

 

[Guest]

Hello Ray, try this in D1

=AVERAGE(OFFSET(INDEX(B$1:B$365,MATCH(C1,A$1:A$365,0)),-6,,7,))

copy down column

 

[Roger Govier]

Hi Ray

You can do the calculation with Offset, but I prefer the non-volatile
Index Method

=AVERAGE(INDEX(B:B,(C1-1)*7+1):INDEX(B:B,(C1-1)*7+7))