1. ## 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. ## 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. ## 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. ## 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. ## Re: Fomula? (9.0.6926 (SP-3))

<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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Fomula? (9.0.6926 (SP-3))

My shortened version should fix this also.

Steve

14. ## 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. ## 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 Last

#### Posting Permissions

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