Forum Discussion
Excel Baseball 1st Batter Out Formula Help
Attached is an example file I use for tracking pitchers statistics. I'm struggling with finding a formula that works for figuring out if the pitcher got the first batter out in the inning.
I've successfully built a formula which identifies if it is the 1st batter of the inning (column BH).
However, the issue is that the at bat rarely finishes on the first pitch. So I need excel to look for if the Outcome (column P) of that 1st batter was a safe/positive result or an out/negative. You can tell when the batter changes based on column E. But I'm struggling to connect the "1" produced in column BH with the Outcome in column P since they are rarely in the same row.
I've thought about trying to get the "1" produced in column BH to only be there when the batter in column E changes. But again, not sure where to start with that.
If you need an example for the formula for safe/out result, you can look in BL/BM when I calculate Last Batter Safe and Out.
10 Replies
From what I gather of your request: the outcome essentialy is did the batter reach base or did not reach base (e.g. Struck out, lined out, etc.). The issue is the data is granular so the outcome must be checked across multiple columns to see what happened to the batter. This is do-able with BYROW or even MMULT. Before a formula can be drawn up it would be helpful to note which of the columns are "Out" and which are "Reached base".
Sorry, different accounts there Patrick. That reply was from me, the original author!
I'm not sure if you want the results aggregated and if you need a formula to also determine P (Outcome) automatically. This formula produces a summary of counts the pitcher retired for the first batter for a given inning:
=LET( crit, (outcome = "K") * (batter = 1) + (outcome = "O") * (batter = 1), GROUPBY(inning, batter, COUNTA, , , , crit) )
I currently don't have a column that represents out or safe. Column P is where you put the result of the at bat. Scrolling over to the right are all my calculations/formula in red that help me do all my data.
I could easily put two more columns amidst that which would dictate an out/negative or safe/positive result. If you look in column BL & BM, you can see I've formatted something that is calculating that for a different situation.
So if I made two separate columns, one would be:
Safe: IF(OR(P2="1B","2B","3B","HR","BB","HBP"),1,0)
Out: IF(OR(P2="K","O","ROE"),1,0)
That would give you what you need for the result of the at bat. Not it is just trying to match that up with the 1st batter of each inning. Any ideas on how to do that now?
