Current Streak

[BradH]

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.

 

[MARK858]

Have you tried the first function that Joe4 posted in post #13 in the thread below?

http://www.mrexcel.com/forum/excel-questions/92222-formula-calculating-win-loss-streaks-2.html

 

[BradH]

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