Finding the longest sequence of 0's in a row

[MJW]

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.

 

[Allllen]

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.

step 2

now you can just use the =MAX() function on this new set of columns that you
have created

 

[Bernie Deitrick]

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

 

[T. Valko]

Assuming there are no empty cells within the range.

Try this array formula** :

=MAX(FREQUENCY(IF(rng=0,COLUMN(rng)),IF(rng<>0,COLUMN(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)

 

[Peo Sjoblom]

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

 

[Peo Sjoblom]

Doh!


--


Regards,


Peo Sjoblom

Assuming there are no empty cells within the range.

Try this array formula** :

=MAX(FREQUENCY(IF(rng=0,COLUMN(rng)),IF(rng<>0,COLUMN(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)

 

[Gaurav]

=COUNTIF(A1:Z1,0)

Is that what you need?

 

[Peo Sjoblom]

I don't think so


--


Regards,


Peo Sjoblom

=COUNTIF(A1:Z1,0)

Is that what you need?