# Calculating maximum of moving average values across varying periods

1. ## Calculating maximum of moving average values across varying periods

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?

Thank you for help.  Register To Reply

2. ## Re: Calculating maximum of moving average values across varying periods

=MAX(INDEX(SUBTOTAL(1,OFFSET(\$B\$22,(ROWS(K\$22:K22)-1)*24+ROW(\$A\$1:\$A\$24)-1,,\$E\$22)),))

or
=MAX(INDEX(SUBTOTAL(1,OFFSET(\$B\$22,MATCH(I22,\$A\$22:\$A\$101)+ROW(\$A\$1:\$A\$24)-1,,\$E\$22)),))  Register To Reply

3. ## Re: Calculating maximum of moving average values across varying periods

Thank you for all your help! Nice solution.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 