Hello again excellents!
I am working on a spreadsheet that is a collection of events in a game of cricket. I am aware that lots of people do not know cricket, so I will try and explain (I have attached a dummy spreadsheet) ... but the headings are as follows:
Col A: MatchInns
Col B: Bowler
Col C: Over
Col D: Day
Col E: Session
Col F: Spell
Col G: Spell2 (=COUNTIFS($B$2:B2,B2,$C$2:C2,C2,$D$2:D2,D2,$E$2:E2,E2,$A$2:A2,A2))
Basically, a Bowler A will have a series of overs that run alternatively with another Bowler B, when Bowler A is replaced by Bowler C, he is said to have bowled a Spell. So in a pattern of A-B-A-B-C-D: Bowler A will have 1 Spell and Bowler B will have 1 Spell ...if Bowler A return (A-B-A-B-C-D-A), then he starts a 2nd spell and this continues 1,2,3 .... n. A spell can be broken by either a new bowler, change in MatchInns (ColA), Change in Day (ColD) or a change in Session (ColE), I have manually completed Column F and tried to use the Countifs formula in Col G but, I am not getting the results I need.
I realise I am struggling to explain this very well but, basically a series is dependent on several factors that stop it but allow it proceed to the next number but, only col A stops the series completely and restarts it at 1 again.
I am very sorry that I am not able to explain this better, I appreciate that it might be difficult to get your heads around my drivel but if anyone can it is this forum!
Thanks in advance.
Bookmarks