We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

how to count max string of successive zero's [closed]

asked 2016-04-15 12:26:32 +0200

corjan gravatar image

i am analysing the seasonability of non-occurance (=0) . i have a table with 52 columns which represent the weeks of the year.

i would like to count the max string of successive zeros in a row to see how long there is no occurance.

how can i do this?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-09-03 22:42:27.712883

1 Answer

Sort by » oldest newest most voted

answered 2016-04-15 15:40:49 +0200

Lupp gravatar image

updated 2016-04-15 16:27:00 +0200

(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 columns (which may be hidden, of course).

See 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?)

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2016-04-15 12:26:32 +0200

Seen: 52 times

Last updated: Apr 15 '16