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.
|