Need formula to calculate the average number of consecutive cells

[ilford3200]

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

 

[Héctor Miguel]

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.

 

[ilford3200]

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

Best wishes,

Ilford