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.

​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.    Read More

​