Current Streak

BradH

New Member
Joined
Jan 25, 2010
Messages
44
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

BradH

New Member
Joined
Jan 25, 2010
Messages
44
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,551
Messages
5,636,965
Members
416,952
Latest member
prakashkumar

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?

Disable AdBlock

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
Top