Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the longest sequence of 0's in a row
Hi All,
Ok, I can't think of any remotely-easy way to do this. My data exists as such: accounts listed in the rows, dates (by month) listed in the columns; in the fields corresponding to each account/dates is the quantity of items the account purchased in that month. Is it formulaically possible (barring VBA) to have Excel calculate the longest contiguous string of zero-value cells for each row? (What I'm trying to discern is a historical gap-analysis for the longest no-purchase period of a given row/account.) Any thoughts or suggestions are greatly appreciated, as always. Thanks, Jamie W. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the longest sequence of 0's in a row
ok i have just done this but in 2 steps
it is not very clean but it works and is easy to understand it relies on you having enough empty columns at the right (or you can adapt it for another sheet) step 1 leave a blank column after your dates then start with this formula in the next column and drag it across and down until this new area is the same size as your data area with the dates in it =IF(A1<0,0,IF(K1=0,1,K1+1)) [assuming A1 is the first date and K1 is the empty column you just made] the basic idea is that as you spread the formula across and down it makes a running total of how many 0s in a row. when it hits a value it goes back to 0. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the longest sequence of 0's in a row
Jamie,
There is no easy way to do that with one formula - but a helper row of formulas would make it easy. For example, if you have values in row 9, starting in column B, then in B8 use the formula =IF(AND(C9=0,B9=0),A8+1,0) and copy to the right to match the values in B9:???9, then use =MAX(8:8) to determine the largest sequence of zero values. Certainly a User-Defined-Function (VBA) is much neater: Insert the code below into a codemodule, and use the function like this =MaxSeq(9:9,0) or =MaxSeq(B:B,0) or =MaxSeq(A1:Z1,0) HTH, Bernie MS Excel MVP Function MaxSeq(r As Range, v As Double) As Variant Dim i As Long Dim c As Range Dim CurCnt As Integer Set r = Intersect(r, Application.Caller.Parent.UsedRange) If r.Rows.Count 1 And r.Columns.Count 1 Then MaxSeq = "Block" Exit Function End If If Application.CountIf(r, v) = 0 Then MaxSeq = 0 Exit Function End If For i = 2 To r.Cells.Count If r(i).Value < "" And r(i - 1).Value < "" Then If r(i).Value = v And r(i - 1).Value = v Then CurCnt = CurCnt + 1 MaxSeq = Application.Max(MaxSeq, CurCnt) Else CurCnt = 0 End If End If Next i MaxSeq = MaxSeq + 1 End Function "MJW" wrote in message ... Hi All, Ok, I can't think of any remotely-easy way to do this. My data exists as such: accounts listed in the rows, dates (by month) listed in the columns; in the fields corresponding to each account/dates is the quantity of items the account purchased in that month. Is it formulaically possible (barring VBA) to have Excel calculate the longest contiguous string of zero-value cells for each row? (What I'm trying to discern is a historical gap-analysis for the longest no-purchase period of a given row/account.) Any thoughts or suggestions are greatly appreciated, as always. Thanks, Jamie W. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the longest sequence of 0's in a row
Assuming there are no empty cells within the range.
Try this array formula** : =MAX(FREQUENCY(IF(rng=0,COLUMN(rng)),IF(rng<0,COL UMN(rng)))) Example: 1,0,0,1,1,0,0,0,1,0,1,1,0,0 Formula result is 3. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "MJW" wrote in message ... Hi All, Ok, I can't think of any remotely-easy way to do this. My data exists as such: accounts listed in the rows, dates (by month) listed in the columns; in the fields corresponding to each account/dates is the quantity of items the account purchased in that month. Is it formulaically possible (barring VBA) to have Excel calculate the longest contiguous string of zero-value cells for each row? (What I'm trying to discern is a historical gap-analysis for the longest no-purchase period of a given row/account.) Any thoughts or suggestions are greatly appreciated, as always. Thanks, Jamie W. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the longest sequence of 0's in a row
Assume your values are in A1:Z1
=MAX(MMULT(--(A1:Z1=0),(TRANSPOSE(ROW(INDIRECT("1:"&COUNT(--(A1:Z1=0)))))=ROW(INDIRECT("1:"&COUNT(--(A1:Z1=0)))))-SIGN(MMULT(--(ROW(INDIRECT("1:"&COUNT(--(A1:Z1=0))))<=(--(A1:Z1=0)=0)*TRANSPOSE(ROW(INDIRECT("1:"&COUNT(--(A1:Z1=0)))))),--(TRANSPOSE(ROW(INDIRECT("1:"&COUNT(--(A1:Z1=0)))))=ROW(INDIRECT("1:"&COUNT(--(A1:Z1=0))))))))) entered with ctrl + shift & enter will give you the longest streak of zeros, note that if a cell is blank it will be included -- Regards, Peo Sjoblom "MJW" wrote in message ... Hi All, Ok, I can't think of any remotely-easy way to do this. My data exists as such: accounts listed in the rows, dates (by month) listed in the columns; in the fields corresponding to each account/dates is the quantity of items the account purchased in that month. Is it formulaically possible (barring VBA) to have Excel calculate the longest contiguous string of zero-value cells for each row? (What I'm trying to discern is a historical gap-analysis for the longest no-purchase period of a given row/account.) Any thoughts or suggestions are greatly appreciated, as always. Thanks, Jamie W. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the longest sequence of 0's in a row
Doh!
-- Regards, Peo Sjoblom "T. Valko" wrote in message ... Assuming there are no empty cells within the range. Try this array formula** : =MAX(FREQUENCY(IF(rng=0,COLUMN(rng)),IF(rng<0,COL UMN(rng)))) Example: 1,0,0,1,1,0,0,0,1,0,1,1,0,0 Formula result is 3. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "MJW" wrote in message ... Hi All, Ok, I can't think of any remotely-easy way to do this. My data exists as such: accounts listed in the rows, dates (by month) listed in the columns; in the fields corresponding to each account/dates is the quantity of items the account purchased in that month. Is it formulaically possible (barring VBA) to have Excel calculate the longest contiguous string of zero-value cells for each row? (What I'm trying to discern is a historical gap-analysis for the longest no-purchase period of a given row/account.) Any thoughts or suggestions are greatly appreciated, as always. Thanks, Jamie W. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the longest sequence of 0's in a row
=COUNTIF(A1:Z1,0)
Is that what you need? "MJW" wrote in message ... Hi All, Ok, I can't think of any remotely-easy way to do this. My data exists as such: accounts listed in the rows, dates (by month) listed in the columns; in the fields corresponding to each account/dates is the quantity of items the account purchased in that month. Is it formulaically possible (barring VBA) to have Excel calculate the longest contiguous string of zero-value cells for each row? (What I'm trying to discern is a historical gap-analysis for the longest no-purchase period of a given row/account.) Any thoughts or suggestions are greatly appreciated, as always. Thanks, Jamie W. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the longest sequence of 0's in a row
I don't think so
-- Regards, Peo Sjoblom "Gaurav" wrote in message ... =COUNTIF(A1:Z1,0) Is that what you need? "MJW" wrote in message ... Hi All, Ok, I can't think of any remotely-easy way to do this. My data exists as such: accounts listed in the rows, dates (by month) listed in the columns; in the fields corresponding to each account/dates is the quantity of items the account purchased in that month. Is it formulaically possible (barring VBA) to have Excel calculate the longest contiguous string of zero-value cells for each row? (What I'm trying to discern is a historical gap-analysis for the longest no-purchase period of a given row/account.) Any thoughts or suggestions are greatly appreciated, as always. Thanks, Jamie W. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the longest contiguous 1-d array of 0's | Excel Discussion (Misc queries) | |||
Find the Longest Run. | Excel Worksheet Functions | |||
Longest string in a column | Excel Worksheet Functions | |||
Finding numbers missing from a sequence | Excel Discussion (Misc queries) | |||
Longest Entry in Columns | New Users to Excel |