Average length of a string

[ss55]

Hi,

I have a row of 1's and blanks, where there might be 10 consecutive 1's, followed by four consecutive blanks. I want to calculate the average length of a string of 1's in the row -- does anyone have any ideas as to how this might be accomplished?

Thanks.

 

[shg]

=AVERAGE(IF(FREQUENCY(IF(A1:A30=1, ROW(A1:A30)), IF(A1:A30<>1, ROW(A1:A30)))>0, FREQUENCY(IF(A1:A30=1, ROW(A1:A30)), IF(A1:A30<>1, ROW(A1:A30)))))

The formula MUST be confirmed with Ctrl+Shift+Enter rather than just Enter.

 

[ss55]

this formula seems to give me the a value that is very close to =COUNTIF(A1:A30, 1)

maybe there is a typo in the formula?

 

[shg]

Post an example that's wrong.

 

[ss55]

so, let's say beginning in C5 and ending in X5, we have the following:
1111111 111 1 111

there are 14 total 1's
the average length of a string is 3.5 -- (7+3+1+3)/4

your formula produces the number 14 when applied to this row

 

[shg]

If the data is disposed in a row instead of a column, change ROW(...) to COLUMN(...)

 

[ss55]

works - thanks!

 

[shg]

You're welcome.

 

[turnip]

=AVERAGE(IF(FREQUENCY(IF(A1:A30=1, ROW(A1:A30)), IF(A1:A30<>1, ROW(A1:A30)))>0, FREQUENCY(IF(A1:A30=1, ROW(A1:A30)), IF(A1:A30<>1, ROW(A1:A30)))))

The formula MUST be confirmed with Ctrl+Shift+Enter rather than just Enter.

I've just spent the better part of the day trying to figure this out, but I think I just need to ask.

Is there a way to modify that formula to count the number of times the sequence of 1s exceeds a certain threshold? Say I want to know the number of times it exceeds 10--is that possible?