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.
|