Forum Discussion
Excel Baseball 1st Batter Out Formula Help
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) )- maybe for a particular [inning] something like:
=SUMPRODUCT((D:D=[inning])*(E:E=1)*MMULT(--(P:P={"O","K","BOE"}),{1;1;1}))
or just in total
=SUMPRODUCT((E:E=1)*MMULT(--(P:P={"O","K","BOE"}),{1;1;1}))- Through your reply, I realized there may be a simple solution. Just comparing first batter of inning to outcome. But I have an error I can't figure out.
Safe formula: =IF(AND(E2=1,OR(P2="1B","2B","3B","HR","HBP")),1,0)
Out formula: =IF(AND(E4=1,OR(P4="K","O","ROE")),1,0)
It is only pumping out a 1 if it is that first listed situation (1B or K). Where am I messing up?
- Are you essentially looking to find out how many times the pitcher retired the first batter of a given inning?
- Yes that is exactly it. Getting the first batter out is important so I'd love to have an average for that.
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?
