# @Pivot Table and analysis of weather data

Highlighted
Occasional Contributor

# @Pivot Table and analysis of weather data

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.

11 Replies
Highlighted

# Re: @Pivot Table and analysis of weather data

@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.

Highlighted

# Re: @Pivot Table and analysis of weather data

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?

Highlighted

# Re: @Pivot Table and analysis of weather data

@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.

Highlighted

# Re: @Pivot Table and analysis of weather data

Oh my...That simple.  I was trying concatenate among other things.  Thanks for clarifying and I appreciate the help.

Highlighted

# Re: @Pivot Table and analysis of weather data

@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.

Highlighted

# Re: @Pivot Table and analysis of weather data

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.

Highlighted

# Re: @Pivot Table and analysis of weather data

@monicard01 Did you group by day? (Screenshot taken on a Mac, but similar on a PC)

Highlighted

# Re: @Pivot Table and analysis of weather data

Not sure where grouping shows up.  I do not see that as an option when I click on the row labels arrow.

Highlighted

# Re: @Pivot Table and analysis of weather data

@monicard01 Right-click on a date within the pivot table. Then the menu should show.

Highlighted

# Re: @Pivot Table and analysis of weather data

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.

Highlighted

# Re: @Pivot Table and analysis of weather data

@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.