Results 1 to 15 of 18
Thread: Fomula? (9.0.6926 (SP3))

20031212, 03:25 #1
 Join Date
 Sep 2001
 Location
 Etobicoke, Ontario, Canada
 Posts
 198
 Thanks
 0
 Thanked 0 Times in 0 Posts
Fomula? (9.0.6926 (SP3))
I keep track of my favourite sports team's scores. In a single column I record each game's results. Example: F2= "53 W" for a win or F5= "42 L" for a loss or F11= "11 T" for a tie. There are also overtime wins and losses. Example F21= "32 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>

20031212, 07:09 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Fomula? (9.0.6926 (SP3))
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.

20031212, 20:50 #3
 Join Date
 Sep 2001
 Location
 Etobicoke, Ontario, Canada
 Posts
 198
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Fomula? (9.0.6926 (SP3))
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 "54 W (OT)" is also worth 2 points.
Sheesh, sorry. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

20031212, 21:12 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Fomula? (9.0.6926 (SP3))
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 54. (I think I know the answer, but just to make sure...)

20031212, 21:47 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Fomula? (9.0.6926 (SP3))
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

20031212, 22:16 #6
 Join Date
 Sep 2001
 Location
 Etobicoke, Ontario, Canada
 Posts
 198
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Fomula? (9.0.6926 (SP3))
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>

20031212, 22:18 #7
 Join Date
 Sep 2001
 Location
 Etobicoke, Ontario, Canada
 Posts
 198
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Fomula? (9.0.6926 (SP3))
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>

20031212, 22:32 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Fomula? (9.0.6926 (SP3))
Make sure there is no space before the equal (=) if you copied it in.
The first cell entry must be an equal sign.
Steve

20031213, 00:28 #9
 Join Date
 Sep 2001
 Location
 Etobicoke, Ontario, Canada
 Posts
 198
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Fomula? (9.0.6926 (SP3))
There was no space. Let me review some examples: If cell A1 contains "21 W" then I want cell A2 to show "2" points. If cell A1 contains "21 W (OT)" then I want cell A2 to show "2" points. If cell A1 contains "21 L (OT)" then I want cell A2 to show "1" point. If cell A1 contains "11 T (OT)" then I want cell A2 to show "1" point. If cell A1 contains "21 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>

20031213, 00:59 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Fomula? (9.0.6926 (SP3))
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))

20031213, 02:25 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Fomula? (9.0.6926 (SP3))
<P ID="edit" class=small>(Edited by sdckapr on 12Dec03 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

20031213, 02:40 #12
 Join Date
 Sep 2001
 Location
 Etobicoke, Ontario, Canada
 Posts
 198
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Fomula? (9.0.6926 (SP3))
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>

20031213, 03:13 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Fomula? (9.0.6926 (SP3))
My shortened version should fix this also.
Steve

20031213, 19:07 #14
 Join Date
 Sep 2001
 Location
 Etobicoke, Ontario, Canada
 Posts
 198
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Fomula? (9.0.6926 (SP3))
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: "43 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 "33 T" and scores 1 point. Your formula records the point for a "33 T" as "0". <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

20031213, 19:44 #15
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Fomula? (9.0.6926 (SP3))
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)))