google-sheetssampling

Determine time that minimum value occurred using Google Sheets

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

    enter image description here