2

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

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.