Hello,
I would like to ask if is there a possibility that can allow specifying row range to average in the calculation of max moving average? I have massive data for 1h measurements and would like to calculate the maximum diurnal (24h) value of 8h moving averages (from 5 pm previous to 5 pm current day). I used array formula to estimate max moving averages of day
=MAX(SUBTOTAL(1,OFFSET(B$22,ROW(B$22:B45)-MIN(ROW(B$22:B45)),,$E$22)))
, but for the next day I need to be able to find new row(range) with offset of 24h (ROW(B$46:B69)-MIN(ROW(B$46:B69)) instead of ROW(B$22:B45)-MIN(ROW(B$22:B45))). Could I use double OFFSET or change ROW number/range function based on INDEX and MATCH combination for matching criteria of next day (…”>=”1/2/2000 17:00 and “<”1/3/2000 17:00…)?
What is the additional Function for Dynamic RANGE calculations (every nth value in the range) in max moving average to a specified Day?
Attached I add test.xls
Thank you for help.
Bookmarks