First 2 of 3 (or 2 before 2) Baseball Data Formula

kyledupic 

I'll step you through my solution.

1. I setup dynamic ranges for 'batter' and 'strike'

'batter' dynamic range
=LET(b, Sheet1!$B$2:$B$10000, nonblank, COUNTA(b), TAKE(b, nonblank))

'strike' dynamic range
=TAKE(Sheet1!$E$2:$E$10000, ROWS(batter))

then created 'uBatter' - a unique pull from the 'batter' dynamic range
=UNIQUE(batter)

2.  Created a Lambda called 'BallsAndStrikes' to be called from REDUCE.

This function filters the data by the batter.  It takes the top 2 rows from the 'strike' results, sums it, and then divides by 2.  If there were two strikes for the first two pitches the result will be 1.  Anything less than 1 is rounded down by INT and not summed in the end.

=LAMBDA(a,v,LET(filtered, FILTER(strike, batter = v), HSTACK(a + INT(SUM(TAKE(filtered, 2)) / 2))))

 3. REDUCE

=REDUCE(0,uBatter,BallsAndStrikes)

For the %, we simply divide the results of the formula above by the number of batters.