Dealing with timestamps and averages over 5 minutes

**n_ant** · 04-28-2013, 05:35 PM

This is the data I'm working with. Every NBBO midpoint corresponds to a tradetime. What I need is the average midpoint over the next 5 minutes of a given trade. For example, with the first trade (at 09:00:13), I need the average midpoint up till 09:05:13, and then like this for every trade. Any ideas?

**Lemice** · 04-28-2013, 06:01 PM

Hello,

These might be the formula you want, yes, there are two with two way of calculating. Paste either of them on C2 and drag them down.

=AVERAGEIFS($B$2:$B$7412,$A$2:$A$7412,"<="&$A2+TIME(0,5,0),$A$2:$A$7412,">="&$A2)

This first formula is the general one-for-all-purpose formula, which will find the average of all midpoint with the tradetime in the range of the tradetime in A2 and A2 plus 5 minutes.

The second formula

=AVERAGEIF($A2:$A$7412,"<="&$A2+TIME(0,5,0),$B2:$B$7412)

This will run faster and does not take as much resources as the first, but only under one condition: you have sorted the tradetime column.

Note that if you paste them on C2 and D2, then drag them down, they will slightly different from each other by an amount of smaller than 0.01 at the best, and smaller than 0.0001 on most other cases, because Excel does not calculate the decimal number with precision, so even a simple calculation like
1*(0.5-0.4-0.1)
will not return 0, so expect a very small amount of difference in results from them.

Here is your sample with both formula, the first on column C, the second on column D, and column E showing the tiny difference.