+ Reply to Thread
Results 1 to 15 of 15

CURRENT Win/Loss Streak

  1. #1
    Registered User
    Join Date
    05-10-2005
    MS-Off Ver
    Office 2007 & 2010
    Posts
    67

    CURRENT Win/Loss Streak

    I have searched through past posts and a common question is the longest winning streak using rows of values in a column. My question (actually three parts) is almost the opposite.

    I am building an NFL tracker to use in making picks on who will win or lose for an office pool. A few pieces of data I would like to create are the CURRENT streaks. These can be wins or losses, just want it to be current. In addition to a general display of thecurrent streak, I would like to show the current streak at home or away (3 streaks displayed in all).

    I have a tab with a summary of each teams season, being fed from a stats page. The summary will update weekly as games are played.

    As an example, line 72 shows if the games are" at" (away) or" hosted" (home) spanning from E72 to U72. Line 73 would show the opponent. Line 74 are the points for the team being summarized, and 75 are the points allowed (opponents score). I have on line 78 a" win" or "loss" for that game.

    I would like to display on line 78 the current win/loss streak, 79 the streak at home" hosted", and line 80 the streak away" at". Columns E thru U can be used as an engine with a punch line displayed in column B78, B79 and B80.

    Additional lines can be added for further engine formulas if needed. Any help is greatly appreciated. Once again it is the CURRENT streak and can be either winning or losing.

    Thanks again.
    Last edited by lil_ern63; 12-06-2010 at 01:49 AM.

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: CURRENT Win/Loss Streak

    You would be best to provide a sample sheet so a responder has something to work with instead of building imaginary data from scratch.

  3. #3
    Registered User
    Join Date
    05-10-2005
    MS-Off Ver
    Office 2007 & 2010
    Posts
    67

    Re: CURRENT Win/Loss Streak

    here is a sample of the sheet. I have copy/paste special>values the data on this tab and removed all the other tabs to shrink the file size.

    The formulas and data used to analyze Arizona would be duplicated for the remaining teams.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: CURRENT Win/Loss Streak

    Just having a look at your file and I notice you haven't considered a Tie score. I know it's pretty rare BUT it has happened and most recently it occurred 2 years ago (Bengals/Eagles). Do you plan to incorporate that scenario?

  5. #5
    Registered User
    Join Date
    05-10-2005
    MS-Off Ver
    Office 2007 & 2010
    Posts
    67

    Re: CURRENT Win/Loss Streak

    I hadn't thought about it actually, given it is so rare. If it doesn't throw the formulas off too much, I would include it, otherwise, that would have to be a "cross the bridge when we get to it" scenario.

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: CURRENT Win/Loss Streak

    I take it you're wanting a formula solution as opposed to VBA? (I would go with VBA if it was my file.) I think the formulas are going to be pretty complex and way above my level.

    You should indicate (preferably in your profile) what version of Excel you're using so that someone doesn't spend time coming up with a solution involving formulas not available to older versions.

    One other thing - are you aware of a possibility that NFL will go with an 18 game schedule as early as 2012? Or is that another bridge?

  7. #7
    Registered User
    Join Date
    05-10-2005
    MS-Off Ver
    Office 2007 & 2010
    Posts
    67

    Re: CURRENT Win/Loss Streak

    The file was created and is used in Excel 2003.

    I am not against using VBA. I typically do use the built in fuctions provided by Excel to do my calculations, and despite some becoming rather cumbersome, I usually am able to fix any bugs that arise with them. If VBA is the only/best way to do what I am hoping to accomplish, I will just have to spend a little more time figuring out the code.

    I have heard of the possibility of an 18 week season. Would that change up things terribly? I know it would take some work on my part to create additional space to accomodate the additional weeks, but it shouldn't affect the win/loss streak formula. Unless it greatly changes the formula, I would consider this another "when we get to it" situation.

    Thanks for the quick responses.

  8. #8
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: CURRENT Win/Loss Streak

    Progress report:

    I am going to try to go with formulas because I think that is your preference

    First - remove the zeros from the score areas
    In Cell E77 place this formula:
    =IF(E74="","",IF(E74>E75,"Win",IF(E75>E74,"Loss","Tie")))
    Drag that formula across to Cell U77
    In Cell E78 place this formula:
    =IF(E74="","",1)
    In Cell F78 place this formula:
    =IF(AND(F72<>"BY Week",F74=""),"",IF(F72="BY Week",E78,IF(F77=E77,E78+1,1)))
    Drag that formula across to Cell U78
    In Cell B78 place this formula:
    ="("&INDEX(E77:U77,1,COUNT(E78:U78))&" - "&INDEX(E78:U78,1,COUNT(E78:U78))&")"

    That should give you the current overall Streak

    I'll get back to you when I have more - the "Honey Do List" beckons

  9. #9
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: CURRENT Win/Loss Streak

    OK, here you go. I added 2 helper rows directly under the "Scores" area but you can hide them easily enough or just format them with a white font.

    It works for the Cards and also when I copied the entire section and pasted it (after inserting rows) under the Falcons but I haven't tried it on any other team. It appears to be working and ready to go for tomorrow's games.

    Let me know if there are any problems with it.

    REMEMBER TO REMOVE ALL ZEROS FROM EACH SCORING AREA (unless game has been played and a zero is final score)
    Attached Files Attached Files
    Last edited by Cutter; 12-05-2010 at 08:31 AM.

  10. #10
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: CURRENT Win/Loss Streak

    Sorry but I thought of a couple of errors that would be triggered by formulas at start of season with no games played so I have corrected those. Here is corrected copy.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-10-2005
    MS-Off Ver
    Office 2007 & 2010
    Posts
    67

    Re: CURRENT Win/Loss Streak

    I will give them a go and let you know if it works out. Thanks for all you help.

  12. #12
    Registered User
    Join Date
    05-10-2005
    MS-Off Ver
    Office 2007 & 2010
    Posts
    67

    Re: CURRENT Win/Loss Streak

    Cutter,

    I tinkered with the sheet you posted, and it all appears to work with one exception. The current streak seemed to start over after a by week. I noticed this on Atlanta when the win streak was 1 prior to the by week and 1 after the by week and both were wins.

    I adjusted the formula starting on F80 to the following:
    =IF(AND(F72<>"BY Week",F74=""),"",IF(F72="BY Week",E80,IF(OR(AND(E72="BY Week",F79<>""),F79=E79),E80+1,1))) to account for the week following a BY Week.

    Other than this, everything else appears to work great.

    Thanks again for all your help!

  13. #13
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: CURRENT Win/Loss Streak

    Hey, glad you have it all working.
    Sorry about the faulty formula. I got doing some other things and missed that glitch.

    Here is a fix that is a bit simpler than the one you are using:

    =IF(AND(F72<>"BY Week",F74=""),"",IF(F79=IF(E72="BY Week",D79,E79),E80+1,1))

    It would go in cell F80 and drag across to end of season. Then copy to each other team.
    I haven't tested it on every team but it works for the first 2 and they have the only 2 possibilities that the formula checks for.

    Enjoy the rest of the season.

  14. #14
    Registered User
    Join Date
    10-19-2011
    Location
    Port Charlotte, Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: CURRENT Win/Loss Streak

    I tried to input the formula you suggested about streaks for wins and losses, I still get #REF at result. I wonder if that is because of 2010 Excel Starter is causing the result? If I buy premium Excel, will it make the results come out right?

  15. #15
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: CURRENT Win/Loss Streak

    Hello palerider85,

    Welcome to Excelforum.....but please read the rules - you just broke rule number 2. You shouldn't "hijack" old threads - please start your own - if you think this one is relevant then include a link, thanks
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1