How to calculate the average of minimum values

I have an Excel 2010 worksheet that contains dates in one column and hourly temperatures in another column. The data is for about ten years - so the worksheet contains about 75 000 rows.
How can I calculate the average of the minimum temperatures for a specific day (say for 20 March) ?
Or an average maximum temperatures as a matter of fact.
I use day of the year for the dates.

Regards and thank you.

I like this issue
Can you upload example workbook so we can see how it's organised.

Hi zbor

Wow that was quick. Thank you - I have attached the workbook. Two sheets one is the raw data and the second is the result.

I shortened the data sheet for size requirements when uploading.

Regards

Daddylonglegs

Good point. How about using date formatting (eg. 20-Mar or 20-03) to evaluate dates?
Havn't tried it but will do so.

Regards

Here. use a Pivot table:

Zbor

Thanks for your reply. Its however not what I'm looking for.

Your Pivot table gets a minimum value for the date. I need an average of all the minimum temperatures over the years for that date.

For example if you take 1 Jan - the first minimum value on 1 Jan 2003 is 11 and the next minimum value on 1 Jan 2004 is 8.7. The average minimum value would be (11 + 8.7 = 19.7 / 2 = 9.85 ).

Also, why can't I set my format to 1-Jan (or 1.sij in Croatian?) with format >custom > d-mm?



Thanks for your help.

Here are two more column...

Since they are array function keep them on smallest range 'cause it will be slow...

Thanx guys

Zbor - I cannot figure out how to set my date column to dd.mm and use that in the pivot table.

Hope you can help - I'm using Excel 2010.