Excel: Count until, then repeat?

Question

I have a list of numbers which are either 1's or 2's. What I'd like to do is count how many 1's there are before a 2 appears, and then keep repeating this down the list (i'm trying to find the average number of 1's between each 2).

What would be the best way of doing this considering I've got over 10,000 rows? (i.e. too many to do manually)

Answer 1

The average number of 1's between each number 2, is the same as the ratio between the number 1 and the number 2.

Example:

1
1
2
1
1
1
1
2
1
1
2
1
1
2

Contains 10 ones and 4 twos.

Or there are five groups of ones, with the following counts: 2, 4, 2, 2

Either way, it will give you and average of 2.5 (10/4 = 2.5)

Note: You have to make a design choice, regarding how to handle beginnings and ends. If you had another one, after the last two, how should it be handled?

Answer 2

You can use the formula as shown in the screenshot below:

Note that the formula in the first row is different.

B                   C
=IF(A2=1,B1,B1+1)   =COUNTIF(B:B,B2)      
=IF(A3=1,B2,B2+1)   =IFERROR(IF(A4=2,COUNTIF(B:B,B4),"")-1,"")

Then to get the average use:

=AVERAGEIF(C:C,"<>"&0)

Noceo's solution as a formula:

=COUNTIF(A:A,1)/COUNTIF(A:A,2)

The output of all the above: