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!

0
Utf
12/28/2009 9:22:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
3312 Views

Similar Articles

[PageSpeed] 45

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


0
T
12/28/2009 9:53:51 PM
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!
> 
0
Utf
12/29/2009 12:39:04 AM
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!
> 
0
Utf
12/29/2009 12:41:04 AM
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!
> 
0
Utf
12/29/2009 12:43:06 AM
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!
> 
0
Utf
12/29/2009 12:45:01 AM
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!
> > 
0
Utf
12/29/2009 3:48:01 AM
Reply:

Similar Artilces:

Is there any limit for the size of std:string???
Hi, I made some code that compresses video data. And It lookes OK if I compress data very much (few bytes) and not so well if data is not compressed at all (many bytes). I have there a lot of variables but I think there is something about std:string. Is there any limit for the size of std:string??? Your question vary vague, but the answer to the question "Is there any limit for the size of std:string?" is (as far as I know) computer memory. My concern is that std:string is not a very good medium for holding video data, since a 0 will terminate the string! And I am pretty sure th...

How can I find strings of wildcards in Excel?
I was looking to find strings made up of wildcard characters in Excel, and I've been having some problems. For example, I was searching for the string "*?" in my spreadsheet (without the quotes), and it was directing me to every cell with data in it. If I search for "{*" (without the quotes), it directs me to every cell with data that begins with the "{" amperscand character. Does anyone have a similar problem, or know how to get around this? Thank you in advance. Hi search for ~* or ~? -- Regards Frank Kabel Frankfurt, Germany "Nick M" &...

Empty (NUll) value in cell, not empty string?
I have cell (D1) with this formula: =IF(ISNA(VLOOKUP(C1,Navne,2,FALSE)),"";VLOOKUP(C1,Navne,2,FALSE)) If C1 is N/A, it returns an empty string. The problem is, you cant multiply an empty string. So my next formula =D1*10 it gives me a VALUE error. I would like to give me a 0 in stead. I know I can change ti formula to insert a zero instead of the "", but is there other way you can do it? I would like to avouid the zero-value in D1. I know I can make conditonal formatting of D1, or suppress the showing of zeros. Jan I forgot to say, the reason I cannot use a zero in ...

Formatting negative numbers #2
I have a text file that gets imported into Excel on a regular basis. The negative numbers come into the spreadsheet in this format: 4000.00- and I want to know if I can write a macro to change all those instances to: -4000.00, moving the negative sign to the proper place. Anyone know if that can be done. In the past we used an old dos application to do that prior to importing it into Excel, but I want to get away from that if we could and just let Excel do it. There are other places in the file that contain text with dashes in it, so I want it only to find the numbers with neg...

Prevent Duplicate Receipt numbers in Bank Transactions
Is there a way to prevent a duplicate receipt number from being posted when entering a Bank Transaction - Receipt? -- TC TC, The receipt number is generated incrementally after Bank Rec has been setup. Why are you getting duplicate receipt numbers? Duplicate how? Are you trying to prevent a receipt for the same dollar amount from the same party? If this is the case, there is little you can do to prevent this from a programatic perspective. You will rather have to rely on old fashioned research and good judgement by your Cash Management team. Best regards, -- MG.- Mariano Gomez, MIS...

Counting records with in a range of a feild
I'm having problems trying to automate the count of records that have a date in a feild that lies between the range of txtStartDate and txtEndDate. Any tips or suggestions? ...

adding a count column
I need to add a column that gives a unique number to each row (except the first or label row) in an excel database. From #1 on. How do I do that? Thanks. Jeff Select an empty column................A for example. In A2 enter 1 Right-click on the fill handle and drag down as far as you wish. Release button and "Fill Series" Gord Dibben MS Excel MVP On Wed, 24 Nov 2010 14:50:50 -0500, "Jeff@nospam.invalid" <Jeff@nospam.invalid> wrote: >I need to add a column that gives a unique number to each row (except >the first or label row) in an excel databa...

Generating numbers in order with excel PLEASE HELP
I looked all over the place and can not find my answer Here my project I have to create number string ab1000000 thre ab1500000 So in excel it was to look like this Ab1000000 Ab1000001 Ab1000002 Ab1000003 And so on all the way until ab1500000 Can anyone help m -- color192 ----------------------------------------------------------------------- color1928's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2573 View this thread: http://www.excelforum.com/showthread.php?threadid=39144 Its actually quite simple. Enter the first two numbers into the columns they go in....

Number Formats HELP?
I have a field on a form that i want to be able to enter a number in decimal format and keep it exactly the way i enter it (ex: 155.5). However the field is automtically rounding the number off to 156 or 155. I've played around with a few format combinations but it continues to round the numbers - Any suggestions??? Adam, What we need to consider is what value is stored in your table vs. what value is shown on the form. That's probably due to the type of numeric field that you defined in your table design. Byte, Integer, and LongInteger can not take a decimal va...

Automated Numbering of Shapes
Good day, I am using Visio 2003 Professional. I am trying to automate the process of using numbered shapes. My issue relates to formatting the properties so that I can employ a specific format. As an example a process shape from the Flow Chart template has to have a corresponding document number in the format DEPT-PC-XXX-YY Where Dept = two letter designator such as OP for operations PC desingates a process but this is selected from a fixed drop down list in the properies dialog. WF, PC, PR WI, FORM XXX designates a specific number like 100 YY designates a sub numbering control num...

2007 treating Text as a Number
2007 on Vista Haven't used excel much lately, so, forgive me please, I am a bit rusty I've got a list of data which I will be importing into my crm software Before I do that, I was going to label each contact (row) with where I got the data from In excel I simply added a column and put "V LBM 260608", into the cell I then formatted the cell as text (normally, I'd just prefix the data with " ' " When I drag this down the column, excel is incrementing each item of data by 1 , which would be ok if it was a number, but it is 'text' How do I stop exce...

Counting instances of a time in cells with date AND time.
Hi all, I'm kind of new and in a bind. I have one worksheet that has column of cells that have a date and time. For example. A 1 Time_Entered 2 4/5/04 9:00:00PM 3 4/6/04 7:40:00AM ... And it goes down. Now, in another worksheet (but you can assume th same worksheet for explaining's sake), I have to count how many time Column A had a time of 7:00:00AM. Any time between 7:00 and 7:59, i other words. I don't know how to count it with the date also in th way. I CAN'T make a new column that has HOUR(A:A) however, that's wha ...

occurence count
I've got a row representing 31 days in a month. If a worker calls in sick, the hours missed goes in that cell. That could be any number up to 8 hours. That absence may carry into many days. I need to calculate the number of occurences. How many times did they call in sick that month? Assume worker calls in sick: 3rd,4th,5th Assume worker calls in sick: 8th Assume worker calls in sick: 21st,22nd That's 3 occurences. What's the simplest formula to show 3 occurences? thanks! One way: Assume A1:AE1 are days of the month A2:AE2 is where you enter the hours missed (if any) oth...

Frond Production Count
Hi - need some assistance here .. I have a table that has a date column, number of fronds, palm number, block number The date is for when the recording date of number of fronds for each palm is done. I would like to create a query that picks up a palm (there can be up to 16 palms in a block) in a block and calculate the avg of frond between the date of the first recording and the second recording. Do I use a cross tab query or a normal query - Please help thanks, On 13 Apr 2007 17:45:11 -0700, "c8tz" <ccholai@gmail.com> wrote: >Hi - need some assistance here .. >...

Find last number
Hello. This Excel beginner could use your help. I would like to have cell g3 display the last number added to range B2:B76. Any help is appreciated. TIA MamaB MamaBee, here is one way, =INDEX(B2:B76,MATCH(9.99999999999999E+307,B2:B76)) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "MamaBee" <MamaBee@ns.sympatico.ca> wrote in message news:uXnAg.33011$pu3.439891@ursa-nb00s0.nbnet.nb.ca... > Hello. > This Exc...

Custom number formats
Hi, today I watched a webcast for easy charts on excel, in there it was mentioned that you can use formulas to change the color of a data label customizing the numbers, my question is: How can I use it to define for a section? (I need to get red colored for values upper than 10%, yellow for values between 10 and 8%, and normal for below) I tryied based on my experince to use the following formulas: [red][>0.1]0%;[yellow][<0.1 and >0.08]0%;[black]0% but doesn't work Tha actual formula i'm using is the following: [red][>0.1]0%;[black]0% I don't want to use macros, t...

Sorting numbers and doing a counta
I have a spread sheet that has repeating numbers, i.e. techtip #'s, i multiple columns. I need to sort these numbers and do a counta on them. Example below: Column A Column B Row 1 1212 4545 Row 2 4545 1212 Row 3 1212 4545 Ideally, I need to have, at the bottom of each column, the sorte techtip #'s and how many are there of each techtip # (counta). Because of the number of techtip #'s, manually sorting and counting i out of the question. Thanks in advance, Jef -- tmi!tmi ------------------------------------------------------------...

Datagridview confused on row count
I have a windows form with the datagridview that displays data from a single table. The underlying data can be updated/deleted from another component. In our case, we raise an event when any underlying changes happen. We then have been trying to refresh our grid without much luck. We use: this.DataSet.Offers.Clear(); this.offersTableAdapter.Fill(this.DataSet.Offers); this.offersBindingSource.ResetBindings(false); this.offersDataGridView.Refresh(); We have tried multiple variations but the underlying data is fairly small. Unfortunately, if a row is deleted often the grid will t...

converting time (format) to number
Hi I am using ms access and I would like to make querry that will convert certain time (ex. 00:25:05) to number of seconds can anyone help since I can not find the solution Thanks Time is stored as a fraction of a day. Multiply the time value by the number of seconds in a day (86400). ?#00:25:05#*86400 1505 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Boge" <boge.nikolov@gmail.com> wrote in message news:1146838648.460933.44820@i40g2000cwc.googlegroups.com... > Hi > I am using ms access and I would like to make querry...

Calculating number of weekdays
Could someone please tell me how to calculate the number of weekdays between two dates eg. between 21/09/2001 and 14/02/2002. I would also like to know how to(a) add and subtract weekdays to a particular date and (b)convert weekdays into calendar days eg.101 weekdays is approximately 144 calendar days. In other words I need to calculate a time period either with weekends included or weekends excluded. Thanks for your help Tom Tom, You need NETWORKDAYS and the WORKDAY functions. The syntax is =NETWORKDAYS(start,end,holidays) =WORKDAY(start,days,holidays) where holidays is either...

Huge number of Text Boxes!
Hello All, I have an Excel file which only has one page. I use it draw a roster like table so that it doesn't contain complex formula. However, I don't know why, its file size is very big ( more than 7M )! I tried to figure out the reason, I realized there are a lot of text boxes ( more than 40000 ) which are empty! So it is very slow whenever I move or change anything in the file. You know, I can't manually delete all of them as there are SO MANY! Moreover, I can't search "Text Box" by the search function. What can I do? Why this happened? Help! R...

Converting Text to Number in 2000
I have a spreadsheet that has a sum formula at the end of a column. Th problem is that the last 7 or 8 values being summed seem to be text. However, if you go to format cells they show up as numbers. In Offic XP you can simply click on the Green arrow in the upper left han corner and select convert to number. This fixes the problem and all i well. In 2000 this function is not available. Is there a way t convert these values without having to retype all the values in? Thanks Bdun -- Message posted from http://www.ExcelForum.com Select the cells. Give them a number format. Data/text to ...

Continuous Numbering
This is a multi-part message in MIME format. ------=_NextPart_000_0019_01C44BEC.83ED0450 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable How does one invoke continuous numbering when there has been a break and = the numbering starts over again? Word has a drop down menu for this, = but I can't figure out how to do so in Publisher. Bill ------=_NextPart_000_0019_01C44BEC.83ED0450 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Tr...

Count using IF / AND
If cell B3 is between a number range and another cell in same Row =(Z) count. As cells are populated the count will increase. So the cell must be between 10 and 20 and the second cell must =Z to count as 1 RERAA wrote: > If cell B3 is between a number range and another cell in same Row =(Z) count. > As cells are populated the count will increase. So the cell must be between > 10 and 20 and the second cell must =Z to count as 1 http://www.contextures.com/xlFunctions04.html#SumProduct 'IF' you gave a clearer explanation 'AND' gave some examples we may b...

Picklist report with Component Lot Numbers
Has anyone gone thru the process of creating a picklist report which includes the lot numbers for the component on the picklist? Just asking to see if its possible and as a pointer to where to get the info from if it is possible.. Thanks heaps Theo Theo: First, I'm assuming you're talking about the default report that prints from the Manufacturing Picklist window. Yes, it can be done, but the process is a little complex, and I'd only recommend that someone very comfortable with Report Writer do it. (And of course, good backups of your reports dictionary.) Also, to print ...