# Convert 5min interval data into hourly data

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.

• Welcome! How exactly does `=SUM(OFFSET('5 minute interval data'!\$A\$1,ROW()-1,COLUMN()*12-23,1,12))` meet your requirements? Feb 28 at 13:07
• @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.
– Rubén
Feb 28 at 15:10

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