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!
CodePudding user response：
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))) |
CodePudding user response：
use:
=ARRAYFORMULA(QUERY({INT(A:A), B:B},
"select Col1,min(Col2),max(Col2),avg(Col2)
where Col2 is not null
group by Col1"))
