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?For the formulas you've listed, you must continue using the cell to check the values.
This is what the first looks like in function arguments. Notice the logicals beyond 1 don't have TRUE/FALSE returns.
Update the formulas -
safe
=IF(AND(E2=1,OR(P2="1B",P2="2B",P2="3B",P2="HR",P2="HBP")),1,0)out
=IF(AND(E4=1,OR(P4="K",P4="O",P4="ROE")),1,0)
- 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.
