CODEWITHSUNDEEP
excelexcel-formulaconditional-statements
StevenM
StevenM

Reputation: 21

Calculate current streak in Excel row

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.

Upvotes: 2

Views: 3079

Answers (3)

Forward Ed
Forward Ed

Reputation: 9874

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

Upvotes: 2

JvdV
JvdV

Reputation: 75870

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

Upvotes: 5

Dominique
Dominique

Reputation: 17491

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.

Upvotes: 0

Related Questions