# Average length of a string

#### ss55

##### New Member
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.

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
=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.

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?

Post an example that's wrong.

ADVERTISEMENT
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

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

Last edited:
ADVERTISEMENT
works - thanks!

You're welcome.

=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?

Replies
4
Views
257
Replies
18
Views
469
Replies
11
Views
523
Replies
1
Views
427
Replies
4
Views
132

Threads
1,196,405
Messages
6,015,094
Members
441,868
Latest member
lukeskiiwalker

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back