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