Formula to count array of 1s?

[QuantEdge]

hi excelers,
Can anyone help me with this problem which I can't find a solution:
I need a Formula to count array SEQUENCE of connecting 1s, which works on any angle and number of cells (see attached examples).

If it is possible, I would be grateful for any suggestions to a solution... thx

PS I hope to calculate this in one excel function?

[canapone]

Hi

is TEXTJOIN function recognised by your Excel?

Example in AU2 confirmed with control+shift+enter

=MAX(IF(ISNUMBER(FIND("-"&REPT(1,ROW(1:10))&"-","-"&TEXTJOIN("",0,SUBSTITUTE(AU4:BC8,"0","-"))&"-")),ROW(1:10)))

In AM2

=MAX(IF(ISNUMBER(FIND("-"&REPT(1,ROW(1:10))&"-","-"&TEXTJOIN("",0,SUBSTITUTE(AM8:AT8,"0","-"))&"-")),ROW(1:10)))

In E2

=MAX(IF(ISNUMBER(FIND("-"&REPT(1,ROW(1:10))&"-","-"&TEXTJOIN("",0,SUBSTITUTE(E4:E8,"0","-"))&"-")),ROW(1:10)))


and so on


I'm using an UDF, because TEXTJOIN is not available on my Excel 2010

Please Login or Register  to view this content.

Regards

[QuantEdge]

thx canapone, but how do I add the UDF?

[canapone]

Ciao

From Google:

Follow along to create custom functions:
Press Alt + F11. ...
Choose Insert→Module in the editor. ...
Type this programming code, shown in the following figure: ...
Save the function. ...
Return to Excel.
Click the Insert Function button on the Formulas tab to display the Insert Function dialog box. ...
Click OK.

As I'm using TEXTJOIN very often, I've preferred to open a new file, add the function (please see again instructions) and save the new file as .XLAM in order to see the new function among the additional excel components.

[canapone]

Hi again

I forgot to tell you that the formulas intercept at most 10 consecutive 1s.

If you'd need to measure longer series, please adjust the two "ROW(1:10)" in the formula.

Quite sure the formulas could produce unexpected results: maybe VBA can offer more robust approach.

Regards

[QuantEdge]

thx canapone, just think there should be a similar solution?

[canapone]

Hi,


I'm afraid I'm not of a great help with the VBA

Hope you'll get other solutions...