I would like to summarise 15min timeseries data in Google Sheets to have aggregated metrics like MIN, MAX and AVG for each date in the series

Question

I'm coming from SQL/R/Python where this question is pretty straightforward but Im having a hard time making headway with it in Google Sheets. I have a dataset that is very similar to stockmarket exchange timeseries data on a 15 minute interval, e.g.

time	value
9/6/2022 17:15:09	1.015
9/6/2022 17:00:08	1.014
9/6/2022 16:45:08	1.010
9/6/2022 16:30:07	1.055

where I have 5 days worth of data. I would like to summarise this data almost exactly as a daily candlestick chart would however not as a graph but as a separate summary table with each unique date in the series as an entry with its corresponding MIN, MAX and Average value e.g.

date	min	max	avg
9/1/2022	1.010	1.055	1.019
9/2/2022	1.012	1.060	1.033
9/3/2022	1.010	1.015	1.014
9/4/2022	1.015	1.020	1.017

Could anyone point me in the right direction. I feel like there's a combination of SPLIT, UNIQUE and VLOOKUP that Im just not getting. Thanks in advance!

Osm · Answer

Use these formulas

Field	Formulas
time	`=ArrayFormula(UNIQUE(DATEVALUE(A2:A)))`
min	`=IF($D2="",,MIN(FILTER($B$2:$B,DATEVALUE($A$2:$A)=$D2)))`
max	`=IF($D2="",,MAX(FILTER($B$2:$B,DATEVALUE($A$2:$A)=$D2)))`
avg	`=IF($D2="",,AVERAGE(FILTER($B$2:$B,DATEVALUE($A$2:$A)=$D2)))`

player0 · Answer

use:

=ARRAYFORMULA(QUERY({INT(A:A), B:B}, 
 "select Col1,min(Col2),max(Col2),avg(Col2) 
  where Col2 is not null 
  group by Col1"))

I would like to summarise 15min timeseries data in Google Sheets to have aggregated metrics like MIN, MAX and AVG for each date in the series

2 Answers