Google Sheets – Get Highest Values for Each Day

google sheets

I have a bunch of raw data in a spreadsheet – dozens of temperature readings a day. My ultimate goal is to graph the highs and lows of each day, filtering out all the other readings. So my data resembles to following:

Date              Temperature
6/16/2016 6:00    71
6/16/2016 12:00   75
6/16/2016 18:00   73
6/17/2016 6:00    82
6/17/2016 12:00   76
6/17/2016 18:00   79
6/18/2016 6:00    73
6/18/2016 12:00   79
6/18/2016 18:00   84

To this end of building a chart, I am trying to filter out all the interstitial readings and output the results into another sheet. Sample output would be:

Date         High    Low
6/16/2016    75      71
6/17/2016    82      76
6/18/2016    84      73

I am using ARRAYFORMULA to get the high low for a given date range (i.e. Todays high is X and the low is Y), but I have been unable to build up a new, filtered dataset of only the highs and lows. I think I need another ARRAYFORMULA but that level of nesting is getting a little too Inception-y for me.

How can I produce a new dataset with only the max and minimum values for each day??

Best Answer

This is the job for query function: 

=query(A:B, "select todate(A), max(B), min(B) where A is not null group by todate(A) order by todate(A) asc label todate(A) 'Date', max(B) 'High', min(B) 'Low'", 1)

Here is the query string with line breaks for readability: it's mostly self-descriptive.

select todate(A), max(B), min(B) 
where A is not null 
group by todate(A) 
order by todate(A) asc 
label todate(A) 'Date', max(B) 'High', min(B) 'Low'

The "todate" command converts a date-time sheet object to a query date object.

Related Topic