Array Formula to Pick Average

[Guest]

I'd like to see if an array formula can save a few columns on a spreadsheet.
I work at a sewage treatment plant. For regulatory purposes we need to
report the highest 7-day moving average concentration for a number of
variables in our effluent. We also need to report the highest 7-day
geometric mean for coliform bacteria. All within a given month. (The bosses
won't be interested in seeing the highest seven-day average that occurred
within the first six days of the month.)

For years we simply configured a column holding 7-day averages (or 7-day
geomean) next to the respective columns holding daily data and used the MAX
function to find the highest running average.

For most of the reports we have monthly data with dates in column A. I've
tried setting up an array formula using the offset function with the
reference set one row above the start of the data and using DAY($a$11:$a41)
to pluck out the row offset. I haven't had much luck.

Similarly, I'd like to convert the files to year-long files (they're
currently monthly). Would there be an array formula that could be used to
pick out the highest seven day average all held within the same month for a
filtered spreadsheet?

Thanks,
EQC

 

[Domenic]

I'm not quite sure I understand, but why don't we start with the
following...

Assumptions:

A1:A31 contains the date (January 1 to January 31)

B1:B31 contains the following corresponding amounts...

1
2
3
4
5
6
7
8
..
..
..
25
26
27
28
29
30
31

....the moving average would be...

N/A
N/A
N/A
N/A
N/A
N/A
4
5
..
..
..
22
23
24
25
26
27
28

....and the highest 7-day moving average would be 28. Is this correct?
If so, try the following formula...

=MAX(SUBTOTAL(1,OFFSET(B1:B31,ROW(INDIRECT("1:"&COUNT(B1:B31)-6))+5,0,-7)
))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Guest]

that worked (at least for everything but the geometric mean numbers, but for
that one variable we may need to simply settle for the extra column since a
geomean is not an option included in the subtotal function anyway.)

Thanks a lot!
EQC

 

[Domenic]

Unfortunately, when it comes to the geometric mean, I'm not aware of a
single formula solution. Maybe others here may be able to help...