Counting a sequence

[Neil Grantham]

I wonder if it is possible to count a best sequence of Wins

My spreadsheet has a column that I enter either Win, Loss or Draw.

I would like to show the best sequence of wins over a whole season, so
not necessarily the last sequence of wins, if that makes sense.

Ideally I'd also like to count the longest unbeaten sequence, given
that both a Win and a Draw are 'unbeaten'
I.E. I might have a sequence that is W W W D W W D L L W W
For the firs part of this email, I want to return 3 as the best Win
sequence, whilst the latter part would return an unbeaten sequence of
7

Possible?

Thanks
Neil

 

[Dan E]

Neil,

Here is one method that employs two helper columns to
accomplish your goal:

Say your column with wins and losses was column A
for examples sake say A1:A100

In B1 put =IF(A1<>"L", 1,0)
In B2 put =IF(A2<>"L", B1+1,0) and drag down as far as
you data goes

In C1 put =IF(A1="W",1,0)
In C2 put =IF(A2="W",C1+1,0) and drag down

To get the longest unbeaten streak
=MAX(B1:B100)
To get the longest winning streak
=MAX(C1:C100)

Dan E