Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Hi All,
I have a single column, dynamic named range called "Data" that contains numeric values. Is it possible without the use of a helper column (or other fill down) to provide a formula that can count the longest consecutive sequence of zeros in "Data" and return that count to a single cell. Sample Data Layout: 1 2 0 0 1 2 3 0 1 0 1 2 0 0 0 0 1 2 0 0 1 2 0 1 2 0 0 0 0 0 0 0 0 0 1 0 0 1 0 Expected Result: Longest consecutive sequence of zeros is 9. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200806/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Hi,
Try this array entered with CTRL+Shift+Enter =MAX(FREQUENCY(IF(A1:A30=0,COLUMN(A1:A30)),IF(A1:A 30<0,COLUMN(A1:A30)))) Note if you have blanks they will evaluate as zero Mike "Sam via OfficeKB.com" wrote: Hi All, I have a single column, dynamic named range called "Data" that contains numeric values. Is it possible without the use of a helper column (or other fill down) to provide a formula that can count the longest consecutive sequence of zeros in "Data" and return that count to a single cell. Sample Data Layout: 1 2 0 0 1 2 3 0 1 0 1 2 0 0 0 0 1 2 0 0 1 2 0 1 2 0 0 0 0 0 0 0 0 0 1 0 0 1 0 Expected Result: Longest consecutive sequence of zeros is 9. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200806/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Ignore that it doesn't work!!
"Mike H" wrote: Hi, Try this array entered with CTRL+Shift+Enter =MAX(FREQUENCY(IF(A1:A30=0,COLUMN(A1:A30)),IF(A1:A 30<0,COLUMN(A1:A30)))) Note if you have blanks they will evaluate as zero Mike "Sam via OfficeKB.com" wrote: Hi All, I have a single column, dynamic named range called "Data" that contains numeric values. Is it possible without the use of a helper column (or other fill down) to provide a formula that can count the longest consecutive sequence of zeros in "Data" and return that count to a single cell. Sample Data Layout: 1 2 0 0 1 2 3 0 1 0 1 2 0 0 0 0 1 2 0 0 1 2 0 1 2 0 0 0 0 0 0 0 0 0 1 0 0 1 0 Expected Result: Longest consecutive sequence of zeros is 9. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200806/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Hi,
One way: If your given data is A1:A39 In B1 enter =IF(A1=0,1,"") In B2 enter =IF(AND(ISNUMBER(B1),A2=0),B1+1,IF(A2=0,1,"")) Select B2 and copy down to the end of your data. Then in another cell: =MAX(B1:B39) Regards - Dave. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Hi Sam,
A simple working solution to your problem is to use a helper column. With your sample data in A2:A40 then =IF(A2=A1,B1+1,1) placed in B2 and dragged down to B40 and in C1 put =MAX(B2:B40) That is a very simple workable solution to the problem that many spreadsheet designers would use yet you don't want helper columns and drag downs which leads me me to believe that you are trying to solve a problem in a competition or the like. If that is the case then do your own dirty work. My apologies if my assumption is incorrect Martin "Sam via OfficeKB.com" <u4102@uwe wrote in message news:85cf16d429f75@uwe... Hi All, I have a single column, dynamic named range called "Data" that contains numeric values. Is it possible without the use of a helper column (or other fill down) to provide a formula that can count the longest consecutive sequence of zeros in "Data" and return that count to a single cell. Sample Data Layout: 1 2 0 0 1 2 3 0 1 0 1 2 0 0 0 0 1 2 0 0 1 2 0 1 2 0 0 0 0 0 0 0 0 0 1 0 0 1 0 Expected Result: Longest consecutive sequence of zeros is 9. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200806/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Oops, sorry. Mis-read your requirements. Thought you said "With use of helper
column..." Regards - Dave. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
You could use a user-defined function like the following:
Public Function CountConsecZeros(RangeToCheck As Range) As Long Dim c As Range, TmpCnt As Long, CurrMax As Long If RangeToCheck.Columns.Count 1 Then MsgBox "RangeToCheck must be in a single column", , "Error" CountConsecZeros = -1 Exit Function End If CurrMax = 0 TmpCnt = 0 For Each c In RangeToCheck Select Case c.Value Case 0 TmpCnt = TmpCnt + 1 Case Else If TmpCnt CurrMax Then CurrMax = TmpCnt End If TmpCnt = 0 End Select Next c CountConsecZeros = CurrMax End Function Paste this function in a VBA module in your workbook. It is called like this: =CountConsecZeros(A1:A39) If you are new to user-defined functions (macros), this link to Jon Peltier's site may be helpful: http://peltiertech.com/WordPress/200...e-elses-macro/ Hope this helps, Hutch "Sam via OfficeKB.com" wrote: Hi All, I have a single column, dynamic named range called "Data" that contains numeric values. Is it possible without the use of a helper column (or other fill down) to provide a formula that can count the longest consecutive sequence of zeros in "Data" and return that count to a single cell. Sample Data Layout: 1 2 0 0 1 2 3 0 1 0 1 2 0 0 0 0 1 2 0 0 1 2 0 1 2 0 0 0 0 0 0 0 0 0 1 0 0 1 0 Expected Result: Longest consecutive sequence of zeros is 9. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200806/1 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Try this *array* formula for the example you posted, using Column A:
=MAX(FREQUENCY(IF(A1:A39=0,ROW(1:39)),IF(A1:A39<0 ,ROW(1:39)))) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Sam via OfficeKB.com" <u4102@uwe wrote in message news:85cf16d429f75@uwe... Hi All, I have a single column, dynamic named range called "Data" that contains numeric values. Is it possible without the use of a helper column (or other fill down) to provide a formula that can count the longest consecutive sequence of zeros in "Data" and return that count to a single cell. Sample Data Layout: 1 2 0 0 1 2 3 0 1 0 1 2 0 0 0 0 1 2 0 0 1 2 0 1 2 0 0 0 0 0 0 0 0 0 1 0 0 1 0 Expected Result: Longest consecutive sequence of zeros is 9. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200806/1 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Hi RD,
Thank you very much for your time and assistance. Your formula has done the job Brilliantly! I replaced the A1 cell referencing with my named range. Very much appreciated. Cheers, Sam RagDyeR wrote: Try this *array* formula for the example you posted, using Column A: =MAX(FREQUENCY(IF(A1:A39=0,ROW(1:39)),IF(A1:A39< 0,ROW(1:39)))) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200806/1 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Hi Tom,
Thank you very much for your time and assistance. I have gone with RD's solution but your user-defined function will most definitely be of use. Thank you again for taking the time. Cheers, Sam Tom Hutchins wrote: You could use a user-defined function like the following: Public Function CountConsecZeros(RangeToCheck As Range) As Long Dim c As Range, TmpCnt As Long, CurrMax As Long If RangeToCheck.Columns.Count 1 Then MsgBox "RangeToCheck must be in a single column", , "Error" CountConsecZeros = -1 Exit Function End If CurrMax = 0 TmpCnt = 0 For Each c In RangeToCheck Select Case c.Value Case 0 TmpCnt = TmpCnt + 1 Case Else If TmpCnt CurrMax Then CurrMax = TmpCnt End If TmpCnt = 0 End Select Next c CountConsecZeros = CurrMax End Function Paste this function in a VBA module in your workbook. It is called like this: =CountConsecZeros(A1:A39) If you are new to user-defined functions (macros), this link to Jon Peltier's site may be helpful: http://peltiertech.com/WordPress/200...e-elses-macro/ Hope this helps, Hutch -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200806/1 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Hi Martin,
MartinW wrote: Hi Sam, A simple working solution to your problem is to use a helper column. With your sample data in A2:A40 then =IF(A2=A1,B1+1,1) placed in B2 and dragged down to B40 and in C1 put =MAX(B2:B40) That is a very simple workable solution to the problem that many spreadsheet designers would use yet you don't want helper columns and drag downs which leads me me to believe that you are trying to solve a problem in a competition or the like. I have multiple columns from which I need to extract the same type of information and trying to avoid additional columns would be very helpful. If that is the case then do your own dirty work. My apologies if my assumption is incorrect Martin Cheers, Sam -- Message posted via http://www.officekb.com |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Hi Mike,
Thank you for reply. RD just pipped you to it with the ROW Function. Cheers, Sam Mike H wrote: Ignore that it doesn't work!! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200806/1 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Longest Consecutive Sequence of zeros
Hi Sam,
I am still intrigued as to why you would want to go with a slow and cumbersome array solution when a simple and faster helper column solution is available. You say you have multiple columns, well, Excel has plenty of columns to spare, there is no crime in using them, you just hide the helpers. Regards Martin "Sam via OfficeKB.com" <u4102@uwe wrote in message news:85d12cab2a061@uwe... Hi Martin, MartinW wrote: Hi Sam, A simple working solution to your problem is to use a helper column. With your sample data in A2:A40 then =IF(A2=A1,B1+1,1) placed in B2 and dragged down to B40 and in C1 put =MAX(B2:B40) That is a very simple workable solution to the problem that many spreadsheet designers would use yet you don't want helper columns and drag downs which leads me me to believe that you are trying to solve a problem in a competition or the like. I have multiple columns from which I need to extract the same type of information and trying to avoid additional columns would be very helpful. If that is the case then do your own dirty work. My apologies if my assumption is incorrect Martin Cheers, Sam -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find longest consecutive winning streaks | Excel Worksheet Functions | |||
Finding the longest sequence of 0's in a row | Excel Worksheet Functions | |||
Counting number of consecutive zeros at the end of a list | Excel Discussion (Misc queries) | |||
Average, Excluding Zeros, Non-Consecutive Range | Excel Discussion (Misc queries) | |||
Inventory numbers - Consecutive, but out of sequence | Excel Discussion (Misc queries) |