Aug 17 2020 05:16 PM
I have a personal weather station with almost 14 years of data recorded at 1/2 hour data points. I have turned this txt data into an excel database which has 225,000 records. In the data is low temperature for each 1/2 hour data point. How would I use pivot tables to extract out the highest low temperature for each day? I am not expert enough at pivot tables to know how to filter data so that I only extract out the highest low temperature for each day rather than the low temp for each 1/2 hour datapoint.
Aug 18 2020 01:47 AM
@monicard01 Perhaps like in the attached (simplified) example?
Not sure what you mean by "highest low temperature", though. I assumed you wanted the lowest temperature on any given day and used "Min." in the value field. Use "Max." if you really want the highest (i.e. warmest) of all the low temperatures.
Aug 18 2020 09:14 AM
That seems to work. I wanted to find out which days had the highest minimum temperature. How do you concatenate the date and time fields together?
Aug 18 2020 09:31 AM
@monicard01 In my example I just entered date and time into one cell. But if you have date and time in two different cells, make sure that they are formatted as date and time respectively. Then you can create a time stamp by adding the two together and apply a custom format " dd/mm/yyyy hh:mm ". See attached.
Aug 18 2020 09:40 AM
Oh my...That simple. I was trying concatenate among other things. Thanks for clarifying and I appreciate the help.
Aug 18 2020 09:47 AM - edited Aug 18 2020 09:49 AM
@monicard01 Glad I could help. Simplicity is my middle name :)) Keep in mind that dates and times ar just numbers in Excel, but formatted in a particular way.
Aug 18 2020 09:52 AM
Hmm...I just did the same thing, but all I get is the low temperature for the month. How can I select each date? My row labels only show month.
Aug 18 2020 09:59 AM
@monicard01 Did you group by day? (Screenshot taken on a Mac, but similar on a PC)
Otherwise, upload part of your file, say the first 200 rows only, with your pivot table.
Aug 18 2020 10:04 AM
Not sure where grouping shows up. I do not see that as an option when I click on the row labels arrow.
Aug 18 2020 10:09 AM - edited Aug 18 2020 10:12 AM
@monicard01 Right-click on a date within the pivot table. Then the menu should show.
Aug 18 2020 10:29 AM
That works. Thanks. Any way of ranking the values within the pivot table, for instance highest to lowest. Pivot tables are very powerful as long as you know how to manipulate them.
Aug 18 2020 12:16 PM
@monicard01 Not sure if this is official functionality, but when you select the first cell to the right of the column headers of the pivot table (in my example file that would be F1) and then select "Filter"
... it will put a filter button on the header row(s) in the pivot table. Then you can use it as on any other table with filter buttons.