Spike Count from Time Series Data Sets

[UseLessFuel]

Hi - in need of help again. I require to count the number of large spikes per week from over two years of, mostly hourly, timestamped data from around 50 data sets. Some sets are in half-hourly intervals. Unfortunately there are cases of missing data, ranging from 1 record up to 140 records or more, and one full 24-hour period may consist of 23 to 25 records, so any collating would have to be based on times rather than Row numbers (I think!).

The time-stamps (dd-mmm-yyyy hh:mm:ss) start in cell A2 while the spike data starts in cell B2. The total number or Rows, ranges from around 5,800 to 22,000 per data set.

A large spike is characterised by increasing values over a period of at least 3 hours (i.e. 3 data points in an hourly dataset, 6 points in a half-hourly dataset). Each large spike rises by at least “1” over the 3 hours, so my thought is to identify (and count) the number of occurrences when the data first rises by "> or = 1" over any 3 hour period. There is, at most, only one large spike per day, so there can only be 0 to 7 spikes per week.

Data example:


[TABLE="class: cms_table_grid, align: center"]
<tbody>[TR]
[TD]05-Oct-2011 15:13:15[/TD]
[TD]21.1[/TD]
[/TR]
[TR]
[TD]05-Oct-2011 16:13:27[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]05-Oct-2011 17:13:41[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]05-Oct-2011 18:13:54[/TD]
[TD]20.8[/TD]
[/TR]
[TR]
[TD]05-Oct-2011 19:14:06[/TD]
[TD]21.6[/TD]
[/TR]
[TR]
[TD]05-Oct-2011 20:14:19[/TD]
[TD]22.6[/TD]
[/TR]
[TR]
[TD]05-Oct-2011 21:14:33
[/TD]
[TD]24.3[/TD]
[/TR]
[TR]
[TD]05-Oct-2011 22:14:48[/TD]
[TD]25.7
[/TD]
[/TR]
</tbody>[/TABLE]


No spike is detected between 15:13:15 and 18:13:54 (a 3 hour period), but a large spike would be detected between 18:13:54 and 21:14:33 (with 24.3 – 20.8 = 3.5 which is >1). There are smaller spikes which I am not interested in, which never increase by 1 over 3 hours.

It would be ideal if the number of spikes for each week could be tabulated at the top of the Worksheet (eg starting at cell D1).

I have tried a moving average over 24 and 12 hour periods, but a spike is not readily distinguished this way and copying the formula down, at irregular row-intervals, is too time-consuming. A 6-hour moving average may work, since most spikes rise for between 5 to 7 hours, but is, again, too time consuming to copy down for each dataset.

Is there an array formula (eg Countifs) or <acronym title="visual basic for applications">VBA</acronym> code that could distinguish and possibly tabulate the number of large spikes for each week, given the data supplied?

I hope I have included enough information – let me know if not. Any help would be very much appreciated. My research is on real-time energy use in households. Regards, UseLessFuel

 

[UseLessFuel]

As an update to above question, I have found a simple, but clunky method to determine and count the spikes by using IF(AND(.... and COUNTIF(... functions.

To identify the spike in the above Table as the entry at 21:14:33 which is in cell B118, I used the following combined functions:

=IF(AND(B116>B115,B117>B116,B118>B117,(B118-B115)>1,C117<>1,C116<>1,C115<>1,C114<>1,C113<>1,C112<>1),1,"No SPIKE")

The formula requires that the previous three cells, before B118, that is cells B117, B116 and B115 were each higher in magnitude than the previous cell AND that cell B118 was at least 1 more than 3 hours (which is three cells) previously AND that no 1 appeared in the previous 6 cells. This works good, but will involve lots of Copying down and perhaps a COUNTIF(range"=1") at the top and maybe some tweeking.

If anyone can see a quicker way, using VBA or macro, I'd be delighted to know.