Averaging problem in Excel97

Posted by Graham on October 17, 2000 4:07 AM

I've got a large table of data downloaded from an automatic logger every thirty seconds or so. I need to produce 15 minute averages - which results in about 44 rows in a column being averaged to produce the result.

the problem is that the next 44 rows are for the next 15 minute average. as my summary table is right at the bottom (at row 4000 and something), can I adjust the averaging function so that the first cell looks at the first 44 and then the next cell down in the summary table looks at the second 44 rows and so on

(obviously I could type average(f1:f44) and then average(f45:89) in each cell etc etc down the rows of the summary table but surely there must be a more elegant way?)

thanks
Graham

Posted by mike on October 17, 2000 2:34 PM

Try auto fill it works for me for the same deal

Posted by Tim Francis-Wright on October 17, 2000 8:32 PM

Autofill will work if you need a rolling average;
that is, if the second average will work for
rows 2 through 45, the third for rows 3 throught
46, and so on.

If you need the first average for 1 through 44,
and the second for 45 through 88, etc., you
might try the following:

1. Define the first summary cell with the
name TOP.
2. Define a separate cell (not a summary cell)
to be the name SIZE. Here, SIZE should have
value 44.

I've assumed the the cells are in column F,
starting in row 1.

Starting with the first summary cell (TOP),
the summary formula could be:
=AVERAGE(INDIRECT("F"&1+(ROW()-ROW(TOP))*SIZE & ":F" & SIZE+(ROW()-ROW(TOP))*SIZE))

You can then copy that formula down as far
as you need it.

HTH

Posted by Graham on October 19, 2000 8:19 AM

Thanks (no msg)

no msg

Posted by Graham on October 19, 2000 8:51 AM

well actually...

I thought the last messager had it sussed out but...

the formula seems to do the same as autofill!
What I would like is to be able to have say:

cell f4000 to contain the equivalent of "=average(f1:f44)"
and then f4001 to contain the equivalent of "=average(f45:f99)"
and so on

help!