Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

(Suppose you are using "String" here meaning any range of contiguous cells in one of your data rows.)

If you can accept a user function ("macro") you actually may convert each row of your data into a string of 52 characters using a single function call in one helper column. Looking for the longest chain of zeros will then require a trick based on a formula evaluating in array mode.

The user function I applied in the example is one from my general-purpose toolbox. Of course, you also can write a more specialised one, and thus avouid to rely on the array-formula trick.

A simple solution not relying on user code at all and also being rather efficient can be implemented based on 52(+1 initialising) helper ask68320LongestChainOfZeros001.odscolumns (which may be hidden, of course).

See attached!

(Suppose you are using "String" here meaning any range of contiguous cells in one of your data rows.)

If you can accept a user function ("macro") you actually may convert each row of your data into a string of 52 characters using a single function call in one helper column. Looking for the longest chain of zeros will then require a trick based on a formula evaluating in array mode.

The user function I applied in the example is one from my general-purpose toolbox. Of course, you also can write a more specialised one, and thus avouid to rely on the array-formula trick.

A simple solution not relying on user code at all and also being rather efficient can be implemented based on 52(+1 initialising) helper ask68320LongestChainOfZeros001.odscolumns (which may be hidden, of course).

See attached!

attached!

(Did you consider that chains of zeros at the beginning or at the end of your year may be just parts of factually longer chains partly lying in different years? What about the significance of your results?)

(Suppose you are using "String" here meaning any range of contiguous cells in one of your data rows.)

If you can accept a user function ("macro") you actually may convert each row of your data into a string of 52 characters using a single function call in one helper column. Looking for the longest chain of zeros will then require a trick based on a formula evaluating in array mode.

The user function I applied in the example is one from my general-purpose toolbox. Of course, you also can write a more specialised one, and thus avouid to rely on the array-formula trick.

A simple solution not relying on user code at all and also being rather efficient can be implemented based on 52(+1 initialising) helper ask68320LongestChainOfZeros001.odscolumns (which may be hidden, of course).

See attached!

ask68320LongestChainOfZeros001.ods

(Did you consider that chains of zeros at the beginning or at the end of your year may be just parts of factually longer chains partly lying in different years? What about the significance of your results?)

results?)

Editing
1. Being in a slightly playful mood I added the above mentioned "specialised function" to the example.
2. On this opportunity I noticed that the original ecxample contained some errors caus bay a bad copy/paste operation with contents of hidde columns. Hopfully the rectifications I made cleaned that up.
3. Nonetheless: No guarantee of any kind.
4. Example replaced by the enhancede one without changing the name.