1

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

2
  • 1
    Welcome! How exactly does =SUM(OFFSET('5 minute interval data'!$A$1,ROW()-1,COLUMN()*12-23,1,12)) meet your requirements?
    – JohnSUN
    Feb 28, 2023 at 13:07
  • 1
    @JohnSUN Thanks for your reply. Please don't refrain yourself from downvoting unclear / unhelpul posts, also questions that doesn't show any research effort should be downvoted, flag / vote to close if besides a question not showing research effort is unclear / doesn't include enough details to be proverly answered by power users of webapps. Feb 28, 2023 at 15:10

1 Answer 1

0

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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.