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

[kyledupic]

I'm wondering if this is possible with the current way the data is setup. See attached picture (I don't see an option to upload the actual excel file but am willing to share).

I want to know what percentage of the time in the 1st three pitches of each at bat/batter they face, did they throw 2 or more strikes?

So if the formula "sees" in column F that it is a 1st P (1st pitch of the at bat), it needs to look at the next two rows and see how many strikes (column H). If strikes >=2 then it should output the value of 1. If <2, then it should output the value of 0. That would be one formula where they were successful. 

Then build another formula that "sees" how many times that happened so I could have success divide by total times.

The challenge I'm envisioning is what happens on the picture I attached. F4 would tell the formula to look at the next 2 columns. However, that was the only pitch of the at bat. So you can see that F5 is ALSO a 1st pitch. This means F4 should not register because there wasn't 3 pitches in the at bat. This stat only uses at bats that last for 3 or more pitches.

[#2 · Accepted solution]

@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.