Counting the number of cells between occurrences

[JaYMac1221]

Good Morning All,

New to the board and I'm looking for a formula to count the number of non-occurrences between occurrences. Example (Homerun Hitter 1. How many at bats between each homerun, what is the longest streak between, and then calculte the standard deviation of those streaks.)

Thanks for your help.

 

[kmillen]

Do you have any data you could supply?

 

[JaYMac1221]

I just put together a quick example. How would I calculate the long streak between home runs and a list of all the streaks between home runs. Hope this makes it clearer. Thanks for the help.

At Bat1	Single
At Bat2	Fly out
At Bat3	Double
At Bat4	Home Run
At Bat5	Strike Out
At Bat6	Ground Out
At Bat7	Home Run
At Bat8	Strike Out
At Bat9	Strike Out
At Bat10	Single
At Bat11	Single
At Bat12	Home Run
At Bat13	Ground Out
At Bat14	Fly out
At Bat15	Home Run
At Bat16	Double
At Bat17	Strike Out
At Bat18	Strike Out
At Bat19	Single
At Bat20	Ground Out
At Bat21	Error
At Bat22	Fielders Choice
At Bat23	Home Run
At Bat24	Sacrifice Fly
At Bat25	Strike Out
At Bat26	Single
At Bat27	Double
At Bat28	Strike Out
At Bat29	Home Run
At Bat30	Ground Out

<tbody>
</tbody>

 

[JaYMac1221]

Good Morning All,

I wrote a array formula to calulate the AB's since a hitters last home run{=COUNTA($B$1:$B$30)-MAX(IF($B$1:$B$30="Home Run",ROW($B$1:$B$30)))}, and tried to give an example of the data. I hope this helps you understand the data I'm trying to extract from the data base of baseball stats. Maybe there's a baseball fan out there.
Thanks for any help with this.

	A	B
1	At Bat1	Single
2	At Bat2	Fly out
3	At Bat3	Double
4	At Bat4	Home Run
5	At Bat5	Strike Out
6	At Bat6	Ground Out
7	At Bat7	Home Run
8	At Bat8	Strike Out
9	At Bat9	Strike Out
10	At Bat10	Single
11	At Bat11	Single
12	At Bat12	Home Run
13	At Bat13	Ground Out
14	At Bat14	Fly out
15	At Bat15	Home Run
16	At Bat16	Double
17	At Bat17	Strike Out
18	At Bat18	Strike Out
19	At Bat19	Single
20	At Bat20	Ground Out
21	At Bat21	Error
22	At Bat22	Fielders Choice
23	At Bat23	Home Run
24	At Bat24	Sacrifice Fly
25	At Bat25	Strike Out
26	At Bat26	Single
27	At Bat27	Double
28	At Bat28	Strike Out
29	At Bat29	Home Run
30	At Bat30	Ground Out

<colgroup><col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;" width="78"><col style="width: 48pt;" width="64"><col style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;" width="109"><tbody>
</tbody>