# Calculate current winning streak

#### breilly00

##### Board Regular
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.

Last edited:

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I 'putzed' up the spread sheet example

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.

ABCDEFGHIJK
1TeamGame1Game2Game3Game4Game5Game6Game7Current win streak
2Anteaters11001113
3Baboons111x011
4Chickens0011114
5Dandelions00011x13
6Elephants10x1000
7
8
9TeamCurrent win streakGame1Game2Game3Game4Game5Game6Game7
10Anteaters31100111
11Baboons1111x01
12Chickens4001111
13Dandelions300011x1
14Elephants010x100

<tbody>
</tbody>
Sheet13

Array Formulas
CellFormula
K2{=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)}
B10{=IFERROR(COUNTIF(OFFSET(A10,0,MAX(IF((C10:I10=0)*(C10:I10<>""),COLUMN(C10:I10))),1,20),1),0)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

Last edited:
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

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!!!!!!

Happy to help!

Replies
6
Views
112
Replies
0
Views
153
Replies
2
Views
567
Replies
2
Views
317
Replies
2
Views
263

1,196,515
Messages
6,015,661
Members
441,914
Latest member
VBAllTheThings

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back