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