Count Consecutive string of same number
Hi,
I would like to find a formula that will give me the largest consecutive sting of numbers out of a list. Here is an example of the list (A1:A10000): this answer is 3. A 1 1 blank 1 blank 1 1 1 Thanks! |
Count Consecutive string of same number
Are the numbers to count *really* 1s?
So, you want to find the longest streak of contiguous consecutive 1s? -- Biff Microsoft Excel MVP "andy" wrote in message ... Hi, I would like to find a formula that will give me the largest consecutive sting of numbers out of a list. Here is an example of the list (A1:A10000): this answer is 3. A 1 1 blank 1 blank 1 1 1 Thanks! |
Count Consecutive string of same number
It's not the most eloquent way, but here is ONE way:
http://www.ozgrid.com/forum/showthread.php?t=71645 -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "andy" wrote: Hi, I would like to find a formula that will give me the largest consecutive sting of numbers out of a list. Here is an example of the list (A1:A10000): this answer is 3. A 1 1 blank 1 blank 1 1 1 Thanks! |
Count Consecutive string of same number
Hi,
Excel experts may have much more elegant solutions to your problem. Anyway... Create a helper column (say Column B) as follows. In B1, enter the formula: =IF(ISNUMBER(A1),1,0) In B2, enter the following formula and fill down to the end of the column. =IF(ISNUMBER(A2),B1+1,0) In some other cell, say C1 enter the formula: =MAX(B:B) C1 will display the length of the longest consecutive string of numbers. Please click "Yes" if this is helpful. Regards, B. R. Ramachandran "andy" wrote: Hi, I would like to find a formula that will give me the largest consecutive sting of numbers out of a list. Here is an example of the list (A1:A10000): this answer is 3. A 1 1 blank 1 blank 1 1 1 Thanks! |
Count Consecutive string of same number
Hello andy! Your answer is he
http://www.ozgrid.com/forum/showthread.php?t=71645 -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "andy" wrote: Hi, I would like to find a formula that will give me the largest consecutive sting of numbers out of a list. Here is an example of the list (A1:A10000): this answer is 3. A 1 1 blank 1 blank 1 1 1 Thanks! |
Count Consecutive string of same number
http://www.ozgrid.com/forum/showthread.php?t=71645
-- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "andy" wrote: Hi, I would like to find a formula that will give me the largest consecutive sting of numbers out of a list. Here is an example of the list (A1:A10000): this answer is 3. A 1 1 blank 1 blank 1 1 1 Thanks! |
Count Consecutive string of same number
Hi,
My previous suggestion will count the length of the longest string of ANY numbers. To find the length of the longest SAME-number string, use the following formula in B2. =IF(ISNUMBER(A2),IF(A2=A1,B1+1,1),0) The formulas in B1 and C1 are still the same as in my previous reply. This approach will count the longest string of same numbers even when the data contain different numbers. For example, 0 1 1 1 empty 2 2 1 1 1 1 3 1 will return 4. It will work for your data as well there are only 1s. Please click "Yes" if this is helpful. Regards, B. R. Ramachandran "B. R.Ramachandran" wrote: Hi, Excel experts may have much more elegant solutions to your problem. Anyway... Create a helper column (say Column B) as follows. In B1, enter the formula: =IF(ISNUMBER(A1),1,0) In B2, enter the following formula and fill down to the end of the column. =IF(ISNUMBER(A2),B1+1,0) In some other cell, say C1 enter the formula: =MAX(B:B) C1 will display the length of the longest consecutive string of numbers. Please click "Yes" if this is helpful. Regards, B. R. Ramachandran "andy" wrote: Hi, I would like to find a formula that will give me the largest consecutive sting of numbers out of a list. Here is an example of the list (A1:A10000): this answer is 3. A 1 1 blank 1 blank 1 1 1 Thanks! |
All times are GMT +1. The time now is 06:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com