Forum Discussion
multiple count using different rows comparing multiple columns
This is data from a pool match.
In pool there are games which Is column A
every change of turn by a player can change the inning by .5
as you can see in A11 it is Johns turn in the first game and the inning is 0
then its rick turn and the inning is .5
and on ricks turn (row 10) he fouls and that is reflected in column E
because he fouls it is now johns turn and now the inning is 1 because the change in turn reflects a change of .5 by inning
whenever someone fouls it is ball in hand and reflected in column D
when a player gets ball in hand I want to know if that player ultimately wins the game without giving the turn back to the other player
a win is reflected in column F
so here John won twice without giving the ball back after ball in hand
in game 1 he won on the same turn he received ball in hand
in game 2 he won 2 shots after ball in hand
each win occurred during the same inning as the ball in hand.
i really appreciate everyones assistance. im trying to achieve this with just a formula and not adding additional columns to count like a helper column.
https://1drv.ms/x/s!AnFi6uGE1reki2mZBJz9KxQLchAK?e=D16fGf
Since DexterG_III thinks he has a solution, I'm going to wait and see what he comes up with.
In the meantime, may I do you the favor of pointing out one key feature that might explain some of the confusion here: it was only in reading this final description that I realized the history of this game goes from the bottom up!! I do believe it would be "normal"--though I may be just making a massive assumption--based on history of transactions (the kind of database with which I'm most familiar) when dates are entered they typically begin at the top and go down as history progresses. In that manner, I would expect the game--its scoring, its innings, its whatever-- to begin at the top and proceed downward.
And if I'm not mistaken, those numbers in Column B actually have no direct relationship to the scores; is that right?! They're just time markers (innings)...
Beyond that, what you've explained is how the "yes" entries get made. What I was looking for, and perhaps it's DexterG_III 's explanation that does this--what I was looking for was a clearer explanation of how you waned to go from the data to the results you want; not an explanation of how the entries got there in the first place. I was looking for clarity that begins with the data--whatever it represents--the rules that would underly the formula, in other words. The rules that would answer the questions you got at the start and explain how you got the ones and twos for John....
Does that make sense? Or am I just adding confusion of my own?
- correct column B has no relationship to the score. it only states the in ing the shot/moment occured.
it makes sense what ya mean. overall. i wasnt clear😜 but im thankful for the community here