Hour Averages

[candykicker]

hello,
I have months of data on many different devices, I need to calculate hour averages. The log interval is not constant through all devices and data sets. Is there a way to have my average function read the time to create these hour averages?

The sample sheet I posted is an example of a "perfect" set of data.

[ChemistB]

Try this formula

Please Login or Register  to view this content.

Is that what you are looking for?

You probably don't want to post a "perfect set of data." It helps more if the data shows worse case scenarios.

[MrShorty]

A pivot table or the AVERAGEIFS() function should have no difficulty with this -- if you can get your date/time stamps imported into Excel in a way that Excel will recognize them as date/time (and convert them to appropriate serial numbers). If they get imported as text strings, then it will be more difficult.

You don't say what version of Excel you are using. I know my version (2007) will not like those date/time stamps -- mostly the "T" and "Z" characters. I think getting time stamps that Excel can recognize is very important to making this easy, so I would:
A) Visit the logging machine and see if there is any way to change the text format of the time stamp into something Excel can work with. Mostly this is using a space instead of T between the date and time and no putting a Z at the end of the time stamp.
B) If I cannot change the way the logging machine writes the time stamp, run the text file through a text editor that can do a bulk "replace T with space and replace Z with nothing" process.
C) I prefer to do the find/replace before importing the text file, but, if that is just not possible, import the file into Excel (making sure the date/time column imports as text) and do the find replace in Excel and make sure Excel recognizes the final date/time stamp as a real date/time serial number (test with ISTEXT() and/or ISNUMBER())

Once you have got the date/time stamp and the other data imported into Excel, then you can either:
D) Insert a pivot table using the time stamp as the row label and average of PM2.5 as the value field. Right click on the row labels and group by day and hour.
E) Or use AVERAGEIFS(). Enter your date/time boundaries in appropriate cells (remember to make sure they are date/time serial numbers and not text). Then =AVERAGEIFS($B$3:$B$3000,$A$3:$A$3000,">"&lower date/time boundary,A$3:$A$3000,"<"&upper date/time boundary)

These averages should not be too difficult, if you will take the time to help Excel correctly import the time stamp field.