Prev: subtracting ranges
Next: hello please help i really should have payed more attention in class
From: andy on 28 Dec 2009 16:22
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!

From: T. Valko on 28 Dec 2009 16:53
Are the numbers to count *really* 1s?

So, you want to find the longest streak of contiguous consecutive 1s?

--
Biff
Microsoft Excel MVP


"andy" <andy(a)discussions.microsoft.com> wrote in message
news:949CCD02-18BB-408F-829B-F812C951D5E2(a)microsoft.com...
> 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!
>


From: ryguy7272 on 28 Dec 2009 19:39
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!
>
From: B. R.Ramachandran on 28 Dec 2009 19:41
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!
>
From: ryguy7272 on 28 Dec 2009 19:43
Hello andy! Your answer is here:
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!
>
 |  Next  |  Last
Pages: 1 2
Prev: subtracting ranges
Next: hello please help i really should have payed more attention in class