Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the longest contiguous 1-d array of 0's
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.misc
|
|||
|
|||
Finding the longest contiguous 1-d array of 0's
Assume you have a list like this:
XYZ 2 0 4 0 0 6 0 0 0 1 RST 1 0 0 0 0 4 3 0 0 1 MNO 4 7 0 0 0 0 0 4 0 2 Define this 3x11 array as MyArray. Define another adjacent 3x11 array as MyAccu. Insert Name Define CSet and RSET respectively =COLUMN(INDEX($1:$1,,1):INDEX($1:$1,,COLUMNS(MyArr ay))) =ROW(INDEX($A:$A,1,):INDEX($A:$A,ROWS(MyArray),)) Tools Options Calculations Iterate 11 (your number of columns) Select MyAccu and enter this array formula: =IF(INDEX(MyArray,RSet,CSet-1)=0,INDEX(MyAccu,RSet,CSet-1)+1,0) Adjacent, select a 3x1 column and enter this array formula: =MAX(INDEX(MyAccu,RSet,)) The result should look like this: 0 0 0 1 0 1 2 0 1 2 3 3 0 0 0 1 2 3 4 0 0 1 2 4 0 0 0 0 1 2 3 4 5 0 1 5 You can hide MyAccu if desired. The last column will show the maximum number of adjacent 0's in a row in MyArray Caveat: The last date is ignored to make things simple. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing non-contiguous array cells | Excel Worksheet Functions | |||
Multiplying Contiguous Values in an Array | Excel Worksheet Functions | |||
Finding non contiguous positive values | Excel Worksheet Functions | |||
Finding last column in non-contiguous range | Excel Worksheet Functions | |||
Weighted avg of numbers not in contiguous row-ie array w lookup? | Excel Worksheet Functions |