Curious about a more optimized method

[cman0]

I came up with a solution, but was curious if anyone else knows a better way. Ideally i'd like to avoid VBA for the ease of portability and security.
Attachment 360227

I get dataloggers to log temperature/humidity/battery voltage for our system. This occurs every 10 minutes, so a lot of data can be provided. I'm looking to get what the average values are for each day in an easy list. This way I can pull the data into other documents and for ease of use of other uses. I've attached a portion of the data so it's clear.

I don't want to be touching the original data. This ensures anyone can perform the operation on any new job, and also keeps raw data untouched.

So my thought of the basic process'
- Clean date from logged value (Col - H)
- Determine unique dates from list (Col - M)
- Locate all data in each day (Col - N,O,P)
- Average data for each day (Col - N,O,P)

[vogel997]

sample-temp-logger.xlsx

See attached, eliminated middle step.

[cman0]

The main reason the middle step is needed is to automatically generate the dates in the log. Without it, it just makes the copy/paste anyone using it a little more difficult. I'd need to think how useful it is, I probably can assume the days should always be consecutive.

Interesting. Thanks.

I seem to have a problem. When i run those functions in N,O,P columns i get '0' instead of the numbers present (i'm getting errors from the 'sumproduct' function). Is there some option that i need to have checked to get this working??

[vogel997]

sample-temp-logger.xlsx

try averageif and i added middle step.

[ELeGault]

If you are taking this into another document for later analysis, I would recommend simply linking to the original document

=DOC NAME A1 and drag it across so that it is an up to date workbook but is ALWAYS just the copy. By getting averages for a day you put your self in a limited box... Averages of Averages for when you want to look at a week, month, quarter, year etc... And if the data is as is, just an abundant amount... you may want to just throw it in a Pivot table and throw a couple calculated fields in there! Then the data/report you create is more end user friendly (Add some slicers) as it sounds like the management of data is risky which is forcing you to compile and split the report from the data... Just thoughts -

[cman0]

I'm still having trouble. I think the main reason the middle step originally was (the way it was) was because i couldn't get column 'B' to be handled as a Date/Time.

Apparently it seems you guys can use the original Date Time as a number (and not straight text). Is there some way to make this happen? Whenever i open the examples you guys post i get #Value, #Div/0, or 0 (since the 'B' column isn't being interpreted correctly).

Basically, maybe i'm missing something obvious, but for example

=Datevalue("09/03/2014 11:48:42")
Gives me an error (#Value!), any idea how to fix this?