# Current Streak

##### New Member
Ok, so there are a bunch of posts on this and went through 3 pages of them, but could not resolve my issue.

I did find the following array formula which works, but only if I limit it to the games which have results.

=LOOKUP("zz",G891:G925)&COUNTA(G891:G925)-IFERROR(MATCH(2,INDEX(1/(G891:INDEX(G891:G925,MAX(1,COUNTA(G891:G925)-1))<>LOOKUP("zz",G891:G925)),0)),0)

Here's the problem, I use this spreadsheet to track more than 300 teams and their results of their games. The most games any one team can have is 36, so I have 36 rows built in for each team.

Since the schedules change frequently and games are added all the time, I use an array to pull the games, along with the results from another page that I download.

The results for the games which don't have a result are blank, but contain the Array formula.

Imagine this as my results column.
 W L W W {Array} {Array} {Array}

<TBODY>
</TBODY>

If I modify the formula to calculate only the first four rows, then it will return "W2".

If I include the rows which don't have results (Array formula in cell, otherwise blank), it would return "3".

If I remove the formula from the blank cells which contain the Array formula, it will return "W2" properly.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Yes, I did try that script. Two problems with it, the rows in my range that do not have any data show as #NUM! and cause that function not to work. (I could probably modify it with an IF statement) The biggest difference with that script though, is it only shows the streak that I ask for...In other words, if I want a current Win streak, it will calculate wins.....

The script above determines the type of streak (IE W or L) and the number. So if a team is on a 5 game win streak, it will display as W5, if they are on a five game losing steak, it will display as L5.

Thanks

Replies
4
Views
161
Replies
6
Views
209
Replies
8
Views
581
Replies
4
Views
303
Replies
0
Views
289

1,203,528
Messages
6,055,930
Members
444,835
Latest member

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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