Convert 5min interval data into hourly data

Question

I am looking to convert a year's worth of 5-minute energy data into hourly data.

The data is set up with each day taking up a row and each 5-minute interval taking up a column. Consequently, my data is spread across 365 rows and 288 columns.

I need to keep the data in the same format but with 24 columns (24 hours per day) instead of 288 columns (288 5-minute periods per day).

I need a formula that adds B2+C2+D2+E2+F2+G2+H2+I2+J2+K2+L2+M2 that I can place in B2 of the second sheet named "hourly interval data".

I would then like to be able to drag this formula to the right to add the next set of 12 cells (i.e. n2:y2) and be able to drag it down to do the same in the next row down.

You can find and comment on the google sheet here: https://docs.google.com/spreadsheets/d/1dafA6IRyHTaC5ZTiedizYHpwo0LaNrzy0p7EAU3xL90/edit?usp=sharing

Answer 1

Use bycol(), filter() and byrow(), like this:

=bycol( 
  B1:Y1, 
  lambda( 
    hr, 
    let( 
      rows, filter('5 minute interval data'!B2:KC, hour('5 minute interval data'!B1:KC1 + "0:59") = hour(hr)), 
      byrow(rows, lambda(row, sum(row))) 
    ) 
  ) 
)

Put the formula in cell 'Hourly Interval Data'!B2.

See bycol(), filter() and byrow().