Winning & Losing Streaks

[Mike]

Good Morning All,

Using Excel XP.

I have a worksheet that keeps track of every baseball game that a team
plays. In one of the columns I have a title called "streak", which gives
the current winning or losing streak. Here's a sample:

A B
W/L Streak
---------------------------------
1 W W-1
2 W W-2
3 L L-1
4 W W-1
5 L L-1
6 L L-2
7 L L-3
8 L L-4
9 W W-1
10 W W-2

Is it possible to make a formula that would automatically count the winning
streak as shown in Column B?
Thanks for your help in advance,
Mike

 

[Bernie Deitrick]

Mike,

In cell B1, use the formula

=A1&"-" & ROW()-SUMPRODUCT(MAX(($A$1:A1<>A1)*ROW($A$1:A1)))

and copy down to match your values in column A.

HTH,
Bernie
MS Excel MVP

 

[Mike]

Thanx Bernie for the quick reply, works great!!
Mike

 

[Harlan Grove]

Bernie Deitrick wrote...

In cell B1, use the formula

=A1&"-" & ROW()-SUMPRODUCT(MAX(($A$1:A1<>A1)*ROW($A$1:A1)))

....

Highly inefficient. There's no need to refer to all of col A up to the
row above the one in which the formula were entered. Better just to use
the result of the formula in the cell above. Note that B1 would be a
different formula from B2 and subsequent.

B1:
=A1&-1

B2:
=A2&(IF(A2=A1,MID(B1,2,6),0)-1)

and fill B2 down as needed.