Interrupted time series

[paulgerrits]

Hi!

Hopefully someone is more knowledgeable than I am when it comes to time series data. I have a database of time series data and I need to throw out rows that contain interrupted time series data.

My idea was to create a new rows linked to the current rows which returns "0 for cells that contain no data and "1" for cells that do contain data. Taking the product of the row will indicate whether or not the time series is disrupted (I need to check per row).

Problem is: not all rows need to be completely filled. If the first cells in the row are empty (i.e. "0"s) and the last ones as well but the cells in between contain "1"s I consider these uninterrupted. However, with my approach the product will still return "0" which would indicate an interrupted serie whereas it actually shouldn't.

Anybody an idea how I can control for the start and end of the row with a formula (no VBAs please)?

Many thanks, P.