SOLVED

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

Copper Contributor

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

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?

5 Replies

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

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?

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

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.

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

``=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".

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

Thanks. See the attached version.

best response confirmed by Bignerdguy (Copper Contributor)
Solution

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

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!
1 best response

Accepted Solutions
best response confirmed by Bignerdguy (Copper Contributor)
Solution

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

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!