Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula to Pick Average
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it ! | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Array Formula | Excel Worksheet Functions | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) |