0

I'm a baseball coach creating a Google Sheet to help track pitches and results for my college guys during games so they can see a lot of different information. See attached picture.

I'd like the chart to autofill the "count" column (Columns B and C). In the picture below I manually entered them, but would like them to autofill from the balls and strikes information in columns K and L. Not sure how to do that with unspecified starting and ending points. Also, the count will only ever be up to 3-2. Even if there is another foul ball, the count still stays at 3-2. After an end of the at bat "result" (such as "line hit" or "BB") is selected, I'd like the count to reset to "0-0" and a new "At Bat" is listed. Again, not at all certain if this is even possible without definite starting and ending points.

I could also reformat the cells to be a single cell and have it just be text that populates the cell. Not sure if that would be any easier/better to deal with only one cell instead of two.

Picture

Improve this question
2
  • Can you share your spreadsheet and add some examples with what you would like the data to look like? For ex, list all of the possible ball strike combinations and the corresponding counts. I'm not too familiar with baseball, so the above would be helpful
    – Raymond Tran
    May 5, 2021 at 20:20
  • absolutely. Here is the link. docs.google.com/spreadsheets/d/… The following are all the possibilities (found in range AK42 through AK58). -Strike Options: Take Strike, Swing and Miss, Foul Ball, K, ꓘ, Ground Hit, Ground Out, Ground Error, Fly Hit, Fly out, Fly Error, Line Out, Line Error, Line Hit, Other Result. -Ball options: Ball, BB.
    – Denny
    May 6, 2021 at 22:13

2 Answers 2

Reset to default
0

You want to maintain a running "Count" total

Balls (Column B)

  • =if(isnumber($A5),0,if($K4=1,if(and($B4=3,$C4=2),3,B4+K4),B4+K4))
  • Enter in cell B5 and copy down for every pitch

Strikes (Column C)

  • =if(isnumber($A5),0,if($K4=1,if(and($B4=3,$C4=2),2,C4+L4),C4+L4))
  • Enter in cell C5 and copy down for every pitch

Note: do NOT merge cells B4 and C4

LOGIC -

  • Balls

    • if the value in column A is a number then the count is 0,
      else
    • if previous pitch is a ball AND if the preceding row contains a "3-2" count, then the Ball count = 3
      otherwise
    • add the value in the preceding row of Column B to the previous pitch "Ball" value (Column K)

  • Strikes

    • if the value in column A is a number then the count is 0,
      else
    • if previous pitch is a ball AND if the preceding row contains a "3-2" count, then the Strike count = 2
      otherwise
    • add the value in the preceding row of Column C to the preceding pitch "Strike" value (Column L)

Sample

Screenshot

Improve this answer
2
  • Thank you, that mostly works. However, how can I set the maximum number of strikes to "2" even in different scenarios other than 3-2. I would like, using your example, column c to have a maximum value of 2, and column B to have a maximum value of 3, independent from the other.
    – Denny
    Aug 17, 2021 at 21:35
  • @Denny I'm sorry. I'm not a baseball player so I can't even visualise the scenario that you are trying to describe. I don't understand the difference between question and answer as written, and the new scenario. At a minimum, there needs to be more detail as well as an example of how a successful outcome might appear. If this answer satisfies the original question (as asked), then I suggest that you draft a new question. Also, you might consider accepting this answer.
    – Tedinoz
    Aug 19, 2021 at 1:01
1

That mostly worked. I added an extra if statement to keep 2 as the maximum number of strikes. See below:

=if(isnumber($A5),0,(if($C4=2,2,if($S4=1,if(and($B4=3,$C4=2),2,C4+T4),C4+T4))))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.