Forum Discussion

Brass Contributor

Oct 04, 2022

Stuck on offset counting if conditions met

Any help greatly appreciated! https://1drv.ms/x/s!AnFi6uGE1reki3wSipwTCD-hIefq?e=plHvk0 in the game of pool a player can play a "safety" which can usually will give a difficult shot to the oppon...

excel

Formulas and Functions

DexterG_III

Iron Contributor

Oct 04, 2022

rangelsammon

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?

Dexter

rangelsammon
Brass Contributor
Oct 05, 2022
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.
- rangelsammon
  Brass Contributor
  Oct 05, 2022
  im looking at it more and i think sumproduct might resolve.
  - rangelsammon
    Brass Contributor
    Oct 05, 2022
    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.
    =COUNTIFS(data[safety],"",data[rack],2,data[inning],XLOOKUP("yes",data[safety],data[inning])+0.5)