Need formula to calculate the average number of consecutive cells
 

Hello!

I have a column in Excel consisting of 1's and 0's. I need to
calculate the average size of consecutive cells (groups of cells)
containing 1's.

For example, the average size of consecutive cells containing the
number 1 in the following array is 3 (i.e., (2+4+3+3)/4 = 3):

1100111100000011100011100000


Can someone *please* suggest a formula to do this?

Thanks in advance!

Ilford

Need formula to calculate the average number of consecutive cells
 

hi, Ilford !

I have a column in Excel consisting of 1's and 0's.
I need tocalculate the average size of consecutive cells (groups of cells) containing 1's.
For example, the average size of consecutive cells containing the number 1
in the following array is 3 (i.e., (2+4+3+3)/4 = 3):
1100111100000011100011100000
Can someone *please* suggest a formula to do this?

assuming your array of numbers in [A2:A29]
and you can use [A30] as the "end-cell" (blank or whatever BUT 1's ) -?-

=sum(a2:a29)/sumproduct(--(a2:a29=1),--(a3:a30<1))

if any doubts (or further information)... would you please comment ?
hth,
hector.

Need formula to calculate the average number of consecutive cells
 

Thank you so much, Hector! The formula works perfectly and is easily
modified to work with my data.

Best wishes,

Ilford