Excel Streaks

[TGHMak]

Does anyone know how I can calculate W/L streaks within Excel? I would like to figure out the mean, median and mode of the streaks.


I have the data entered within a single column:

W
1
1
0
1
1
1
0
1
1
0
1
1

[mebaran]

Go to the data tab on top. Click on Data Analysis. Then choose Descriptive Statistics. Choose your data field by highlighting it. Then click summary statistics at the bottom of the box. Also, highlight an output range in your spreadsheet (where you want the stats to show up).

[That Foreign Guy]

I'd do it with a nested if, but I am addicted to them at the moment.

You're checking A16,

=IF(AND(A16=A15,A16=A14,A16=A13),4,IF(AN D(A16=A15,A16=A14),3,IF(A16=A15,2,1))) etc will give you the length of this streak.

you then need to determine if a cell is the end of the streak or not. =IF(B17<B16,"",B16)

If you're interested in telling the difference between W and L streaks I would add this back in based on the value of column A for the streak ending line. =if(a19=1,"W"&c19,"L"&c19)

[mebaran]

I'd do it with a nested if, but I am addicted to them at the moment.

You're checking A16,

=IF(AND(A16=A15,A16=A14,A16=A13),4,IF(AN D(A16=A15,A16=A14),3,IF(A16=A15,2,1))) etc will give you the length of this streak.

you then need to determine if a cell is the end of the streak or not. =IF(B17<B16,"",B16)

If you're interested in telling the difference between W and L streaks I would add this back in based on the value of column A for the streak ending line. =if(a19=1,"W"&c19,"L"&c19)

Oops, definitely misread the question. But to find standard error and means of those streaks, you can still use data analysis

[TomG]

Create a new column next to W. Label it "Streak." Assuming column W starts in A, use this formula for streak.

=IF(A2=A1,B1+1,1)

[djiddish98]

Take the formula that TomG provided, but add this formula next to it in column C

=IF(B2=1,B1,"")

This way, Column C will list the final number of the streak as opposed to simply counting the streak. From there, you should be able to apply =AVERAGE, =MEDIAN and =MODE on column C to get a sense of streaks. I believe the "" in the formula will be disregarded.