How would you calculate a winning streak in excel?

[Eliath]

How would you go about calculating a winning streak for a team in excel?

For example: I track matches, recording wins/losses/draws. I have already
set up excel when I enter the results of the match that it will automatically
allocate the appropriate win/loss/draw in the appropriate column.

I cannot figure out a way to track any streaks. Does anyone know if this is
possible?

 

[GaryO3]

Using the following spreadsheet.....

Cell A1="Home Team"
Cell B1="Away Team"
Cell C1="Win/Lose/Draw"
Cell D1="Current Streak"

Cell A2="7" (Home team score)
Cell B2="10" (Away team score)
Cell C2="Lose" (assumes you're the home team)
Formula for C2 is If(A2>B2,"Win",If(A2<B2,"Lose","Draw")
Cell D2="1 Loss" (see below for formula)

Cell A3="10" (Home team score for 2nd match)
Cell B3="13" (Away team score for 2nd match)
Cell C3="Lose" (same formula as above)
Cell D3="2 Losses"

Here's how to calculate the streak (not elegant maybe, but easy enough):

Add 3 new columns (J, K, L)
Formula in J2 is If(C2="Win",1,0). Formula in K2 is If(C2="Lose",1,0).
Formula in L2 is If(C2="Draw",1,0).
Formula in D2 is If(J2=1, "1 Win", if(K2=1, "1 Loss", "1 Draw"))

Formula in J3 is If(C3="Win",J2+1,0). Formula in K3 is If(C3="Lose",K2+1,0).
Formula in L3 is If(C3="Draw",L2+1,0).

Formula in D3 is
IF(J3=1,"1 Win",IF(J3>1,CONCATENATE(TEXT(J3,"#")," Wins"),
IF(K3=1,"1 Loss",IF(K3>1,CONCATENATE(TEXT(K3,"#")," Losses"),
IF(L3=1,"1 Draw",CONCATENATE(TEXT(L3,"#")," Draws))))))

Copy the formulas down as you need. As I said, not the most elegant but it
works. You can also hide columns J-L to make it look better.