1. 12-14-10 07:27 AM #1
    TGHMak
    Update your status
    TGHMak's Avatar Become A Pro!
    Join Date: 02-24-09
    Posts: 658
    Betpoints: 114

    Excel Streaks

    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
    Reply With Quote Reply With Quote
  2. 12-14-10 08:11 AM #2
    mebaran
    Con los terroristas
    mebaran's Avatar Become A Pro!
    Join Date: 09-16-09
    Posts: 1,540
    Betpoints: 330

    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).
    Reply With Quote Reply With Quote
  3. 12-14-10 08:16 AM #3
    That Foreign Guy
    I got sunshine in a bag
    That Foreign Guy's Avatar Become A Pro!
    Join Date: 07-18-10
    Posts: 432
    Betpoints: 3069

    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
    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)
    Last edited by That Foreign Guy; 12-14-10 at 08:22 AM. Reason: let me know if it isn't clear, you'll need to stretch the if statement until it covers a really long streak
    Reply With Quote Reply With Quote
  4. 12-14-10 08:59 AM #4
    mebaran
    Con los terroristas
    mebaran's Avatar Become A Pro!
    Join Date: 09-16-09
    Posts: 1,540
    Betpoints: 330

    Quote Originally Posted by That Foreign Guy View Post
    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
    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
    Reply With Quote Reply With Quote
  5. 12-14-10 09:35 AM #5
    TomG
    TomG's Avatar Become A Pro!
    Join Date: 10-29-07
    Posts: 500
    Betpoints: 1

    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)
    Reply With Quote Reply With Quote
  6. 12-14-10 12:54 PM #6
    djiddish98
    djiddish98's Avatar Become A Pro!
    Join Date: 11-13-09
    Posts: 345
    Betpoints: 237

    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.
    Reply With Quote Reply With Quote
Top
« Previous Thread | Handicapper Think Tank | Next Thread »