I have a set of sampled data consisting of date/time of sample and sample value for a number of days. I would like to determine the time at which the minimum value for each day is measured:
| Datetime | Value | Note |
|---|---|---|
| 1/11/24 0:00 | 124.8 | x |
| 1/11/24 10:12 | 124.9 | |
| 1/11/24 12:18 | 124.9 | |
| 1/11/24 17:12 | 139.6 | |
| 1/11/24 19:42 | 139.6 | |
| 1/11/24 22:00 | 139.6 | |
| 1/12/24 2:06 | 139.5 | |
| 1/12/24 6:06 | 139.5 | |
| 1/12/24 10:24 | 149.7 | |
| 1/12/24 14:18 | 124.7 | |
| 1/12/24 18:48 | 124.5 | x |
| 1/12/24 22:48 | 124.6 | |
| 1/13/24 2:42 | 125 | |
| 1/13/24 7:18 | 125.5 | |
| 1/13/24 12:00 | 125.4 | |
| 1/13/24 19:00 | 124.5 | x |
| 1/14/24 1:42 | 124 | |
| 1/14/24 10:36 | 124 | |
| 1/14/24 14:06 | 123.9 | x |
| 1/15/24 3:06 | 122.5 | x |
| 1/15/24 8:06 | 160.3 | |
| 1/15/24 12:24 | 161 |
The "x’s" mark the minimum values for ease of reference.
I can find the minimum value using either Query:
=ArrayFormula(query({int(A3:A),B3:B},"select Col1,min(Col2) where Col2 is not null group by Col1 label Col1 'Date'"))
| Date | Min |
|---|---|
| 1/11/24 | 124.8 |
| 1/12/24 | 124.5 |
| 1/13/24 | 124.5 |
| 1/14/24 | 123.9 |
| 1/15/24 | 122.5 |
or Map/Lamda:
={"Date";arrayformula(unique(int(A3:A)))}
={"Min"; ArrayFormula( map( G3:G, lambda( date , if( date , round( minifs( B3:B, int(A3:A), int(date) ),2 ), ) ) ) )}
But cannot figure out how to extract the actual time at which the minimum values occur
A sample spreadsheet can be found at https://docs.google.com/spreadsheets/d/1nbHvJxv2alB9jwc4AJlweaXZvq-ukOHIlvBwnCGhjBc/edit?usp=sharing
Thanks
>Solution :
You may try:
=map(G3:G,lambda(Σ,+sort(ifna(filter(A:B,int(A:A)=Σ)),2,1)))
- output column formatted to custom format
h:mm