Winning & Losing Streaks

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

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" <[email protected]> wrote in message
news:McQ6e.8490$B93.1881@lakeread06...
> 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
>
>

Thanx Bernie for the quick reply, works great!!
Mike
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:McQ6e.8490$B93.1881@lakeread06...
>> 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 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.