7 Replies Latest reply: Jan 31, 2013 2:18 AM by Barry
MNGuy Level 1 Level 1 (0 points)

A football game has 4 quarters so Q1, Q2, Q3, Q4. I'd like to keep track of scoring for the Home and Away teams with 8 columns:

 

HQ1, HQ2, HQ3, HQ4, AQ1, AQ2, AQ3, AQ4

 

The 8 columns above would have the scores input via letters defined in a "Score Key" table #2. The score key table contains 3 columns:

 

Safety, S, 2

Field Goal, F, 3

Point After, P, 1

Touch Down, T, 6

Conversion, C, 2

 

So if the Home Team scores a touch down (6), a point after (1), and a field goal (3), in the first quarter, the string in HQ1 = TPF

 

The above value for HQ1 = TPF could happen again for HQ2, HQ3, and HQ4.

 

Using the "concatenate" function would return the string TPFTPFTPFTPF. A human can sum this string 6+1+3+6+1+3+6+1+3+6+1+3=40

 

I've got the "lookup" function using the "right" function processing the numerical values for the last letter (right) in the concatenated string above, so F=3

 

Getting the other values and adding them is done using the "mid" function and subtracting 11 times for each string location, This is what I've got:

 

=SUM(

          LOOKUP(RIGHT(T(CONCATENATE(A2, B2, C2, D2))),Table 2 :: B2:B6,Table 2 :: C2:C6),

          LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-11,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

                 LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-10,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

                 LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-9,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

          LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-8,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

          LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-7,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

          LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-6,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

                  LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-5,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

                   LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-4,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

                   LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-3,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

          LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-2,1),Table 2 :: B2:B6,Table 2 :: C2:C6)

                  LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-1,1),Table 2 :: B2:B6,Table 2 :: C2:C6))

 

The above sum returns the correct value for the case when the home team scores a TPF in each quarter, totaling 40 points or 12 total score items.

 

Though it works for the situtation above the Mid function blows up when the 2nd arguement is not greater than or equal to 1, so is there a way to do this so the sum wouldn't have to be edited for every combination of scores?


Mac OS X (10.7.5)
  • 1. Re: Convert "string" into numbers and sum by loop over string
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    MNG,

     

    Would you mind posting a screen shot of this sheet? I'm having a little trouble visualizing how you have arranged the data and calculations.

     

    Jerry

  • 2. Re: Convert "string" into numbers and sum by loop over string
    MNGuy Level 1 Level 1 (0 points)

     

     

    Jerrold,

     

    The following link will allow you to download a copy of the Numbers file:

     

    https://www.dropbox.com/s/dtgstlw6hncze1g/String%20to%20Number%20Loopup.numbers

     

    String to Number Loopup 1.png

     

    String to Number Loopup 2.png

     

    The working formula that will total Home Team Score if FTP is in HQ1, HQ2, HQ3, HQ4 is the following:

     

    =SUM(

              LOOKUP(RIGHT(T(CONCATENATE(A2, B2, C2, D2))),Table 2 :: B2:B6,Table 2 :: C2:C6),

              LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-11,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

              LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-10,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

              LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-9,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

              LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-8,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

              LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-7,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

              LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-6,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

               LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-5,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

               LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-4,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

               LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-3,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

               LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-2,1),Table 2 :: B2:B6,Table 2 :: C2:C6),

               LOOKUP(MID(T(CONCATENATE(A2, B2, C2, D2)), LEN(T(CONCATENATE(A2, B2, C2, D2)))-1,1),Table 2 :: B2:B6,Table 2 :: C2:C6)

    )

  • 3. Re: Convert "string" into numbers and sum by loop over string
    MNGuy Level 1 Level 1 (0 points)

    I should mention this would work if in every game a team scored a total of 12 items some combination of SFTPC so just as an example the above worked when:

     

    "FTP" was repeated 4 times that is FTPFTPFTPFTP = 40 points

     

    if SFTP+SFPC+SFTP was the string, because there's exactly 12 characters, the forumla I've got would work, just it's very unlikely that each team would score "12 items" within every game, so it defeats the purpose of having a formula.

     

    My goal would be to get a formula working not matter the case, even if 100 field goals were recorded or nothing was...

     

    Any suggestion is appericated.

     

    Thanks!!

  • 4. Re: Convert "string" into numbers and sum by loop over string
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Thanks for the file. My recommendation would be to add a calculation table to simplify things. Here's an example:

     

    Screen Shot 2013-01-26 at 11.28.14 am.png

    Regards,

     

    Jerry

  • 5. Re: Convert "string" into numbers and sum by loop over string
    Barry Level 7 Level 7 (29,180 points)

    Hi Guy,

     

    The major issue here appears to be translating each occurrence of the coding letter into a numerical value, then summing the resulting numbers.

     

    Here's another approach:

    Picture 8.png

     

    Column A contains the concatenated strings representing the scoring plays achieved by each team over the course of the game. A3 shows your original sample; A3 shows a sample series for their opponent (no idea why they would choose to go for a conversion on that last play!); A4 demonstrates the method's insensitivity to the number of scoring plays recorded.

     

    Play codes are in row 1; point equivalents in row 2.

     

    Basic formula is in B3, and is filled down and right to F5:

     

    B3: =(LEN($A3)-LEN(SUBSTITUTE($A3,B$1,"")))*B$2

     

    SUBSTITUTE replaces each occurrence of the letter in B1 with the zero-length null string, shortening the original string by one character for each target character found. The new LENgth is subtracted from the original string's LENgth to give the number of times that scoring play was successful, and the result multiplied by the point value of that type of score, found in B2.

     

    G3: =SUM(B3:F3)

     

    Regards,

    Barry

  • 6. Re: Convert "string" into numbers and sum by loop over string
    MNGuy Level 1 Level 1 (0 points)

    Thanks so much both of you!

     

    Barry I ended up adding four columns before so I could enter score by quarter, then using:

     

    =CONCATENATE(A3,B3,C3,D3)

     

    got a total "string" for Q1,Q2, Q3, Q4 which is the sum of the game activity. Then to conclude (based on what you provided) I concatenated string is totaled for the value of each game:

     

    =(LEN($E3)-LEN(SUBSTITUTE($E3,F$1,"")))*F$2

     

    THANKS SO MUCH!!!

     

    String to Number.png

  • 7. Re: Convert "string" into numbers and sum by loop over string
    Barry Level 7 Level 7 (29,180 points)

    Hi Guy,

     

    That's what I had intended. You seemed to have the recording part and concatenation of the score codes down, leaving only the translation of the coded score strings into a number, so that was the part I concentrated on.

     

    Thanks for the feedback.

     

    Regards,

    Barry