+ Reply to Thread
Results 1 to 3 of 3

Calculating maximum of moving average values across varying periods

  1. #1
    Registered User
    Join Date
    07-23-2017
    Location
    Wroclaw, Poland
    MS-Off Ver
    2015
    Posts
    5

    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?

    Attached I add test.xls
    Thank you for help.
    Attached Files Attached Files

  2. #2
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,706

    Re: Calculating maximum of moving average values across varying periods

    Please try at J22

    =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)),))

  3. #3
    Registered User
    Join Date
    07-23-2017
    Location
    Wroclaw, Poland
    MS-Off Ver
    2015
    Posts
    5

    Re: Calculating maximum of moving average values across varying periods

    Thank you for all your help! Nice solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1