Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fomula? (9.0.6926 (SP-3))

    I keep track of my favourite sports team's scores. In a single column I record each game's results. Example: F2= "5-3 W" for a win or F5= "4-2 L" for a loss or F11= "1-1 T" for a tie. There are also overtime wins and losses. Example F21= "3-2 L (OT)". Points are assigned as follows: W = 2 points, OT = 1 point and L = 0 points. I want to add a column that will calculate the points. Something like "IF F2 = W THEN G2 = 2 OR IF F2 = OT THEN G2 = 1 OR IF F2 = L THEN G2 = 0"

    Are U still with me? Can somebody help? I have no idea where to start and even if I did I think the OTs will cause me a problem because those cells will also contain an L or W. <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    Say that the scores begin in cell F2. Enter the following formula in cell G2:

    =IF(RIGHT(F2,1)="L",0,IF(OR(RIGHT(F2,1)="T",RIGHT( F2,1)=")"),1,IF(RIGHT(F2,1)="W",2,"")))

    and fill down as far as needed.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    Very cool Hans! How do I learn this stuff without spending $$ on courses?

    There is one problem with the data I provided. I forgot that an overtime win "5-4 W (OT)" is also worth 2 points.

    Sheesh, sorry. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    John Walkenbach writes excellent books. His website contains lots of useful info too. The same holds for Chip Pearson's website.

    Are ALL "... W (OT)" results worth 2 points, or only 5-4. (I think I know the answer, but just to make sure...)

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    How about this:
    <pre>=IF(ISNUMBER(FIND("W",F2)),2,IF(ISNUMBER(FIND ("L",F2)),0,1)</pre>


    If has a "W" then a win (2)
    If no "W" but has an "L then a Loss (0)
    If no "W" or no "L" must be a tie (1)

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    Yes, All "... W (OT)" results are worth 2 points.

    I'll check out those 2 sites next, thanks! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    Steve, that doesn't seem to do anything at all. When I copy and paste it into the cell to the right of the score it just shows up as plain text. <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    Make sure there is no space before the equal (=) if you copied it in.

    The first cell entry must be an equal sign.

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    There was no space. Let me review some examples: If cell A1 contains "2-1 W" then I want cell A2 to show "2" points. If cell A1 contains "2-1 W (OT)" then I want cell A2 to show "2" points. If cell A1 contains "2-1 L (OT)" then I want cell A2 to show "1" point. If cell A1 contains "1-1 T (OT)" then I want cell A2 to show "1" point. If cell A1 contains "2-1 L" then I want cell A2 to show "0" points.

    In other words, if you win in regular time you get 2 points, If you win in overtime (OT) you get 2 points, If you lose in regular time you get 0 points and If you tie or lose in overtime you get 1 point.

    I know it's confusing, but that's Hockey Night in Canada! <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    Try this adaptation of Steve's formula (the result is assumed to be in A1):

    =IF(ISNUMBER(FIND("W",A1)),2,IF(ISNUMBER(FIND("L", A1)),IF(ISNUMBER(FIND("OT",A1)),1,0),1))

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    <P ID="edit" class=small>(Edited by sdckapr on 12-Dec-03 22:25. Added modified formula)</P>Colin,
    Sorry, I missed that a Loss in OT was like a Tie and not a loss.

    Hans,
    Thanks for correcting my oversight.

    Steve
    PS. This is shorter:
    <pre>=IF(ISNUMBER(FIND("W",F2)),2,IF(ISNUMBER(FIND ("OT",F2)),1,0)</pre>


    If has W = win = 2
    If no W, but has OT then Tie or L in OT, so 1
    If neither than Loss = 0

  12. #12
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    I think that has got it - with one exception. I have a points total (the sum of the points column) in the cell under the last regular season game. The formula returns "1" (point) when the game score is blank (the game has not yet been played). <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    My shortened version should fix this also.

    Steve

  14. #14
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    Well, not exactly. (I'm learning the rule book here too). A minimum of 1 point is recorded for any overtime game. Only overtime games that have a decision (W or L) appear in the score as "(OT)". Example: "4-3 W (OT)". A win in OT scores 2 points, a loss in OT scores 1 point. A game that ultimately ends in a tie, even though there was an additional period of overtime played, is recorded simply as "3-3 T" and scores 1 point. Your formula records the point for a "3-3 T" as "0". <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fomula? (9.0.6926 (SP-3))

    This one, then? (Steve will probably come up with a more efficient version)

    =IF(ISNUMBER(FIND("W",A1)),2,IF(RIGHT(A1,1)="T",1, IF(ISNUMBER(FIND("OT",A1)),1,0)))

Page 1 of 2 12 LastLast

Posting Permissions

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