special average

[t.aadland]

I have a time series of data recorded per day in Sheet1. I want to
transform them into weekly averages, and put them into Sheet2.

First I take the average of day 1-7 in Sheet1, and put it in a specific
cell in Sheet2. Then I want the average of the next 7 days from Sheet1
in the next row of Sheet2. But if I just copy the formula in the cell
above, I get the average of day 2-8, and not of day 8-14.

This could be solved by spacing the cells in Sheet2 by 7 lines. But the
trouble is that in my data set some days are missing, so it's not
always an average of 7 cell values - sometimes only 5 or 6 cells need
to be averaged (most likely the missing values should be close to the
other ones).

What I need is sort of a "variable" that can hold the address of the
cells I want to average, and it would be nice if the SUM or AVERAGE
function would accept such variables. If I have an expression exp1 that
evaluates to the address of the first cell in the average, and exp2
evaluates to the address of the last cell, then I would like to do

AVERAGE(exp1:exp2)

But this won't work. Is it possible to solve my problem with worksheet
functions (without using macros)?

 

[Bob Phillips]

Average doesn't care about blank cells, so that is no problem.

Use INDIRECT to get a dynamically defined range, like so

=AVERAGE(INDIRECT("Sheet1!A"&(ROW(A1)-1)*7+1&":A"&ROW(A1)*7))

where this averages A1:A7, A*:A13, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)