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.

[DonkeyOte]

It might be clearer if you post a sample file illustrating what you have - and the desired results based on the sample data.

If the only key interest is the start/end point then perhaps compare the SIGN of first cell to last - if they are the same then the row is uninterrupted ?

[martindwilson]

perhaps =SUM(--(FREQUENCY(IF(A1:G1=1,COLUMN(A1:G1)),IF(A1:G1=0,COLUMN(A1:G1)))>=1)) array entered will tell you if you have a broken sequence of 1's
from the result any number that not 1 is a broken sequence
mind you given your original is times you coild probably apply the a slightly diferent version
against the raw data
=SUM(--(FREQUENCY(IF(A2:G2>=1,COLUMN(A2:G2)),IF(A2:G2=0,COLUMN(A2:G2)))>=1))

[paulgerrits]

Dear both,

Many thanks or the reply, it worked and I am most grateful to you guys!

One more question with which one of you can hopefully help me out:

Now I have deleted interrupted series, I need to convert several series of monthly data (in rows, one range from Oct 1994-June 2009, i.e., in my sheet columns HD:NZ) into Quarterly data.

The Quarterly result should simply be the last month of that Quarter, so for Jan-Mar it should return March for Q1, Apr-Jun should return June for Q2, etcetera. There is, however, one caveat: if a quarter's last month does not contain any data or a value of '0' it should return the data for either the first or second preceding month. In the above examples: for Q1 either February or January, for Q2 either May or April.

Any tips?

Again, many thanks in advance for your wits and help!

Best regards,

Paul

[DonkeyOte]

Per the first post it would be a great idea to post a small sample file that reflects your present set - up and requirements (ie a handful of expected values).

At something of a guess...

Are you saying that

i) you have data in HD:NZ for "n" rows

ii) all rows utilise the same time period (Oct 94 - Jun 09)

iii) for each row you wish to condense the data to 56 columns
(1994 Q4 to 2009 Q2)

iv) the value to be used for each quarter is the last numeric non-zero value to occur in that time frame (this may vary on a row by row basis)