excel sequence count

hi everyone,

 i want to count the number of sequences of 1's in consecutive cells..eg 11100010011001, there are 4 sequences of ones here..can someone tell me what formula to use to do this in excel??

thanks
oggiemc
oggiemcAsked:
Who is Participating?
 
barry houdiniCommented:
if Richard is right then you could also just change the 1s in the formula to "1"s, i.e.

=SUMPRODUCT((A1:A14="1")*(OFFSET(A1:A14,1,0)<>"1"))

Regards, barry
0
 
barry houdiniCommented:
Assuming your data is in A1:A14 you can use this formula
=SUMPRODUCT((A1:A14=1)*(OFFSET(A1:A14,1,0)<>1))
regards, barry
0
 
barry houdiniCommented:
If your range is a horizontal range rather than a vertical one then the OFFSET part changes a little, e.g. if data range is A1:N1 then use
=SUMPRODUCT((A1:N1=1)*(OFFSET(A1:N1,0,1)<>1))
regards, barry
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
oggiemcAuthor Commented:
Hi Barry,
My data is vertical..
 that formula is just giving me back 0. Any ideas??

Runs      Runs of 1
0      0
1       
1       
0       
1       
0       
0       
0      
0      
0      
1      
0      
1


0
 
RichardSchollarCommented:
****No points****

If you select your column of 1s and 0s and go Data>TextToColumns>Finish does the formula now return a value?

If so it would indicate that you had text 1s/os rather than numeric 1s/0s

Richard
0
 
oggiemcAuthor Commented:
thanks guys, it was text values..will split points :)
0
 
RichardSchollarCommented:
Please don't split the points - your solution was all Barrie (and was a very clever solution I thought - much better than the one I came up with which was far too complicated to post).
0
 
oggiemcAuthor Commented:
ok, will give to barry..thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.