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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula to Pick Average
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:"&CO UNT(B1:B31)-6))+5,0,-7) )) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "SludgeQuake" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula to Pick Average
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" wrote: 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:"&CO UNT(B1:B31)-6))+5,0,-7) )) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "SludgeQuake" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula to Pick Average
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... In article , "SludgeQuake" wrote: 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" wrote: 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:"&CO UNT(B1:B31)-6))+5,0,-7) )) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "SludgeQuake" wrote: 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 |
Reply |
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) |