2

I have a list of 1s and 0s in excel row ranging from B2:K2, I want to calculate the current streak of 1's in cell M2,

example dataset where streak would be 4

1 0 1 0 1 1 1 1 0

Is there a simple way of doing this? I have tried research but not been able to find anything specific.

Any help would be much appreciated.

StevenM
  • 21
  • 1
  • 4
  • can we use a helper column ? –  Apr 03 '19 at 11:02
  • are you on a standalone excel (2013 and older) or on the 365 version ? (365 has extra formulas, that's why I'm curious) –  Apr 03 '19 at 11:03
  • I am using office 365, how would I use a helper column? – StevenM Apr 03 '19 at 11:08
  • Difficult to improve on these answers though https://stackoverflow.com/questions/30633434/how-to-find-largest-sequence-of-a-given-number-in-excel – Tom Sharpe Apr 03 '19 at 11:38

3 Answers3

5

Here is a way of doing this with just one formula, no helper columns/rows needed:

enter image description here

The formula used translates to:

{=MAX(FREQUENCY(IF(B1:K1=1,COLUMN(B1:K1)),IF(B1:K1=1,0,COLUMN(B1:K1))))}

Note: It's an array formula and should be entered through CtrlShiftEnter

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    Thank you, this is exactly what I am after! I'll give this a go later when I can run office 365 on windows. (wont allow me to add array formula from a browser) – StevenM Apr 03 '19 at 12:39
2

Assuming your data is layed out horizontally like the image below, the following two formulas should do it for you.

The first cell requires a different formula as the is no cell to the left to refer to. so a simple formula to check if the first cell is one or not is entered in B2.

=--(A1=1)

The part in the bracket will either be true or false. A quirk of excel is that if you send a true or false value through a math operation it will be converted to 1 for true and 0 for false. That is why you see the double - in front. could have also done *1, /1, +0,-0 at the end.

In B2 place the following formula and copy right as needed:

=(A2+1)*(B1=1)

Basically it adds 1 to the series, then check if the number in the sequence is 1 or 0. In the event its one, it keeps the value as it is TRUE sent through the math operator *. If it is false it set the sequence back to zero by multiplying False by the math operator *.

Alternate IF

Now the above while it works and may save a few characters is not necessarily intuitive for most. The go to option would be to use an IF function. The above formulas can be replaced with the following:

A3
=IF(A1=1,1,0)

B3 ->Copied right
=IF(B1=1,A3+1,0)

Longest streak

To get the longest streak, the highest value in your helper row is what you want. You can grab this with the following formula in an empty cell.

=MAX(2:2)

=MAX(A2,I2)

If you have no other numbers in your helper row, you can use the first formula which looks in the entire row. If there are other numbers due to calculations off to the left or right as an example, then you will want to restrict your range to you data as in the second formula.

POC

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • Thank you, I should have mentioned that I have multiple row of this with the first row ad column being consumed by headers. – StevenM Apr 03 '19 at 12:41
0

I've put those values in cells B2 to B8.

In cell C3, I've put this formula:

=IF(AND(B3=1;B2=1);C2+1;1)

Dragging this downto C8, and then take the maximum of the C column.

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • FYI, using your formula, if all values in B2 to B8 were 0 what is your longest streak of 1s? – Forward Ed Apr 03 '19 at 11:32
  • @ForwardEd: you're right. In case there are just zeroes, the formula returns 1, which seems wrong. On the other side, this can be catched by the `Maximum()`: just take the sum of the column. If this is zero, then the result should return zero, else it should return the maximum of the formula column. – Dominique Apr 03 '19 at 12:07