is OFFSET the functcion for me?
 

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.

is OFFSET the functcion for me?
 

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

"Ray Pendergast" wrote:

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.

is OFFSET the functcion for me?
 

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))

--
Regards

Roger Govier


"Ray Pendergast" wrote in
message ...
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.