Calculate current winning streak

[breilly00]

I am trying to calculate only the current winning streak for our Bocceball league. Each game can have 1 of four entries in the game results column. 1= win, 0=loss, x=bye and a blank equals a scheduled game that has not been played yet. There can be multiple byes and scheduled games between/after the games won and it should not 'break' the streak. For Example

Team G1 G2 G3 G4 G5 G6 G7 Streak

A 1 0 0 1 1 1 1 3
B 1 1 1 x 0 1 1
C 0 0 1 1 1 3
D 0 0 0 1 1 x 1 3

I only want the formula to determine the streak of the latest number of wins and ignoring if there are no games played or a scheduled bye.

Thank you in advance for any help.

 

[breilly00]

I 'putzed' up the spread sheet example

 

[Eric W]

It is tricky to post a screenshot here. That's why some smart people developed tools like the HTML Maker (see link in my signature) to make it a bit easier. Even so, I think I figured out your question.

	A	B	C	D	E	F	G	H	I	J	K
Team	Game1	Game2	Game3	Game4	Game5	Game6	Game7	Current win streak
Anteaters
Baboons	x
Chickens
Dandelions	x
Elephants	x
Team	Current win streak	Game1	Game2	Game3	Game4	Game5	Game6	Game7
Anteaters
Baboons	x
Chickens
Dandelions	x
Elephants	x

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]{=IFERROR(COUNTIF(OFFSET(A2,0,MAX(IF((B2:H2=0)*(B2:H2<>""),COLUMN(B2:H2))),1,COLUMN(H2)-MAX(IF((B2:H2=0)*(B2:H2<>""),COLUMN(B2:H2)))),1),0)}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]{=IFERROR(COUNTIF(OFFSET(A10,0,MAX(IF((C10:I10=0)*(C10:I10<>""),COLUMN(C10:I10))),1,20),1),0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



You can put the formula in K2, change the ranges to match your sheet, then confirm by pressing Control+Shift+Enter. Alternately you can shorten the formula a fair bit by moving the formula to the left side of the table and making sure that there isn't anything to the right of the table. See formula B10.

Hope this helps.

 

[breilly00]

team a 1,1,0,0,1,1,1 Streak calculates as 3
team b 1,blank,1,1,x,0,1, Streak calculates as 1
team c 0,0,blank,1,1,blank,1 Streak calculates as 3
team d 0,0,0,1,1,x,1 Streak calculates as 3

 

[breilly00]

Soft zeros and ones float gently across the wires and explode into a "THANK YOU". That is exactly what I was looking for. Moreover, my question was given 2 working opportunities and each was over and above all my expectations.

ERIC, you da man!!!!!!

 

[Eric W]

Happy to help!