# Counting the number of cells between occurrences

#### JaYMac1221

##### New Member
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.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### kmillen

##### Board Regular
Do you have any data you could supply?

#### JaYMac1221

##### New Member
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

##### New Member
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>

Replies
3
Views
253
Replies
9
Views
261
Replies
1
Views
228
Replies
4
Views
314
Replies
1
Views
132

Threads
1,195,632
Messages
6,010,811
Members
441,569
Latest member
PeggyLee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back