Average Time-based data, irregular times 2

[MattGreer]

I am getting data from a data collection system that is time stamped. I'm getting the data at a resolution higher than what I need, so I'd like to average the data over 10 minutes. I can do this in Access pretty easily but I'd like to do this in Excel because others in my office do not have Access.

Data comes in like this, for example:

[tt]
StartDateTime Meter Value
11/12/2013 15:56 FT_METER1 0.014531707
11/12/2013 15:56 FT_METER2 0.010228882
11/12/2013 15:57 FT_METER1 0.013937836
11/12/2013 15:57 FT_METER2 0.002095947
11/12/2013 15:57 FT_METER1 0.014123688
11/12/2013 15:57 FT_METER2 0.002155762
11/12/2013 15:58 FT_METER1 0.014085236
11/12/2013 15:58 FT_METER2 0.002016602
[/tt]

There's more than 2 meters, but you get the picture.

I do not get data every minute for every meter, so the intervals are not always the same.

I tried putting it into a PivotTable, which nicely sorts the Meters into columns, but I still have the difficulty of averaging the values over the previous 10 minutes since there's not always 10 values for every 10 minute increment. It would be nice if the 10 minute increments were at even values, so 10:10, 10:20, 10:30, etc.

Thank you for your help!!!

Thanks!!


Matt

 

[mintjulep]

Average is Sum(Values)/Count(Values)

If you have 10 values in 10 minutes then count is 10.

If you have 7 values in 10 minutes then count is 7.

If you have 897 values in 10 minutes then count is 897.

Doesn't matter how many values you get, sum them up, divide by how many there are.

 

[SkipVought]

hi,

Is StartDateTime really a time stamp and not a START time, which implies that there is an END time?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MattGreer]

mintjulep (nice alias, by the way),

What I'm trying to do is to reduce the data into 10 minute averages. So the data would look like:

11/12/2013 15:50 (average over last 10 minutes)
11/12/2013 16:00 (average over last 10 minutes)
11/12/2013 16:10 (average over last 10 minutes)
11/12/2013 16:20 (average over last 10 minutes)

Maybe I'm thinking about this wrong, but your solution just gives me an average of the entire data set, rather than individual average "blocks". Am I making sense? Do I misunderstand your solution?


Nice catch, skip. There are two dates provided, however, the flow is not totalized in this particular column, so the "finish" time is not needed (although it is there, I'm tossing it out). The start and finish times come in on the same line. To be honest, the engineer I'm working with doesn't know why there are two values. Either way, this issue is something that has plagued me over the past couple weeks which is one of the reasons I turned to Access, other than there are 11 million data entries (and growning) in this particular set...


Thanks!!


Matt

 

[Andrzejek]

The data you provided in the OP has a span of 2 minutes, between 15:56 and 15:58, but you would like to calculate averages over 10 minutes intervals.
I would be nice to see some representative data example and what you would like to have as an outcome.

Do you only look at the date/time to calculate 10 minutes averages and ignore Meter column (FT_METER1 and FT_METER2)?

Have fun.

---- Andy

 

[mintjulep]

=SUMIFS(VALUES, TIME, ">15:40", TIME, ">=15:50")/COUNTIFS(VALUES, TIME, ">15:40", TIME, ">=15:50")

You'll need to work out the syntax.

 

[MattGreer]

Example data. I can't get the formatting to work, but where you see gaps there could be gaps on either column. I may not have data for both meters for any given moment.

mint, I'll give that equation a try here in a while, got a meeting to go to.

[tt]
Meter1 Meter2
11/12/2013 15:56 0.014531707 0.010228882
11/12/2013 15:57 0.004251709
11/12/2013 15:58 0.02830719
11/12/2013 15:59 0.028375549 0.003789062
11/12/2013 16:00 0.008862304
11/12/2013 16:01 0.028749389 0.009697876
11/12/2013 16:02 0.02876648
11/12/2013 16:03 0.00937378
11/12/2013 16:04 0.028579559 0.004179077
11/12/2013 16:05 0.028322144 0.004233398
11/12/2013 16:06 0.015011597
11/12/2013 16:07 0.028389435
11/12/2013 16:08 0.041875458 0.011878662
11/12/2013 16:09 0.0258815
11/12/2013 16:10 0.004224853
11/12/2013 16:11 0.026473236 0.004196167
11/12/2013 16:12 0.027120514 0.011054077
11/12/2013 16:13 0.028558198
11/12/2013 16:14 0.027798767 0.003950806
11/12/2013 16:15 0.027914123 0.015675658
11/12/2013 16:16 0.009637451
11/12/2013 16:17 0.027362976 0.010770263
11/12/2013 16:18 0.027507172 0.003989258
11/12/2013 16:19 0.003925782
11/12/2013 16:20 0.041202546 0.005884399
11/12/2013 16:21 0.02744522
[/tt]

Thanks!!


Matt

 

[zelgar]

For the data with blanks, you'll need to modify mintjulep's code in the COUTNIFS to include VALUES, ">0"

=SUMIFS(VALUES, TIME, ">15:40", TIME, ">=15:50")/COUNTIFS(VALUES, TIME, ">15:40", TIME, ">=15:50", VALUES, ">0")

 

[madonnac]

This is where you would add a helper column - formula would be =FLOOR(A1,TIME(0,10,0))

This will create a column where the time is truncated to 10 minute intervals, with all times from XX:1X going to XX:10, and so on for the rest of the times
A pivot table will then allow you to get averages of the readings for each 10 minute time frame