Stuck on offset counting if conditions met

Brass Contributor

Any help greatly appreciated!



in the game of pool a player can play a "safety" which can usually will give a difficult shot to the opponent which can help the player return to the table. innings change when a players turn changes after miss shot. I am trying to count the average number of shots by the opponent after a safety

for example in rack 2 the answer is 3. 3 after johns safety highlighted. I am trying to do this for the entire match. I think I would be able to do add a contional column and add numbers up that way but im trying to avoid. any help greatly appreciated!

5 Replies



I'm struggling to understand the result of 3.3.


I assume by "after John's safety is highlighted" that you mean the rows above that row based on the inning numbers.  Rick appears to have made 3 shots above that row (2 pockets and 1 miss).    Where does the 0.3 come from, if so?  


If a row where safety = "yes" is the starting point, what's the stopping point?    The subsequent row where safety = "yes"?  Do all shots count regardless of being missed or pocketed?  And should the average span across racks and/or matches vs resetting at those milestones?   





Hi! @DexterG_III l, thanks for offering to help again.


The “.” is actually a period. So the inning is 2. he shot 3 times with the inning 2. 


the inning changes after you miss or change of player.

im looking at it more and i think sumproduct might resolve.
i added a formula to show the count for rack 2 if there was one safety. but there can be multiple safetys in a rack. i want to have in the whole match though.
ya no worries @dexter. im going to use a conditional column to add up when conditions met. i dont think its possible to do without. i was thinking offset and within use height to be a formula to count . but not working