How to calculate average of multiple times for multiple days on a single spreadsheet?

[Bignerdguy]

I am a moderately experienced user of Office and Excel but have run against a brick wall with what I am trying to do.  In a nutshell, I have a spreadsheet with data from a weather station that has rows and columns of data from multiple days and times.  The date column gathers data in the following format:

xx/xx/xx xx:xx  and the temperature column is simple numbers.

So for any given day it has multiple entries for each time slot.  The spreadsheet has multiple days in a long list of rows.  For example, for August it currently (as of this writing) goes from August 1st to August 26 (the current date today) with times for each day starting at midnight and ending at 23:48.

What I want to do is two things: I want to average all days separately but I want to do it for any values above 100.  I don't want to average all the days together (I already have a pivot table doing that).

What makes this complicated is all the days are in one long set of rows and I don't want to have to manually go through and edit the sheet to separate out the days, I'd like to try doing this with a formula since I am doing this for every month in the year and that is a lot of work if I have to do it manually.

Can anyone tell me how to structure a formula that will let me do this?  

[#2]

@Bignerdguy 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

Here is the sample file:

https://1drv.ms/x/s!AtDwlCVLZRa0jGsIeKYc81vEL3bV?e=Dq2nfi

Let me know if you cant access the file. Not sure if I shared this right. I usually don't use OneDrive for anything.

[#4]

@Bignerdguy 

First add headers to your list.

Then add a helper column "Temp >100?" to your data:

=B2>100

Use this helper column in the report filter of a pivot table and filter for TRUE.

"Timestamp" in rows area and group by year, month, day.

"Temp" in values area. Change calculation to "Average".

[#5]

@Bignerdguy 

Thanks. See the attached version.

[#6 · Accepted solution]

Thanks everyone! Got some great ideas but the one I went with used the LET command to setup the details then the AVERAGE/COUNT to get the necessary info I needed. Thanks to all that replied!