[SOLVED with pivottable] Managing long date and time stamped lists

Hello all. I have a challenge in that I have a very long list of data points that represent a years worth of data (approximately 36,000 points). The data is electrical metering data. Most of the data is date and time stamped as follows:

DATE #

2/1/2011 1:30 3419
2/1/2011 1:45 3389
2/1/2011 2:00 3418


So, it's a years worth of data every fifteen minutes---BUT, there are holes in the data and there are random off time data points--meaning an additional point at a random time occasionally (like a point at 2:04 in the above list). For my evaluations, I don't care about the minute by minute numbers. I really care more about the day to day values such as average, min, and max. I'd like to create a solution for manipulating this data that isn't totally brute force.

1) The date/timestamp is in the spreadsheet with a format of: m/d/yyyy h:mm

If I could change the date field to only show the date (eliminate the h:mm) that would give me some ability to consolidate data.

2) If I could find a formula or means to extract the average/min/max on a daily basis that would be great. This is very challenging though as the daily values are generally consistent, but due to data collection issues or the random #:04 data there is no consistent length to a data set.

any assistance would be greatly appreciated.

Mike

1) Assuming that the date in A1 cell

Formula:

Please Login or Register  to view this content.

2)average/min/max - As described try that functions to get your output. If you need guidance then post a sample workbook with expected output and the method needs to be considered for arriving it.

Hi txmxer and welcome to the forum,

Excel has Pivot Tables that allow you to group your data by day and show Min, Max and Average. Easy Peasy... See my example.