defining a consecutive cells criteria

[daniel proville]

in a range, (column or row) I need to count the cells which are positiv
and adjacent to at least one positive cell. then nest this into a ma
function to find out the maximum number of consecutive occurences of
criteria. I thought about using max & countif, but i dont know how t
define the adjacent cells in the formula.
thnks for yr help
danie

 

[immanuel]

Given the following assumptions:

1. The range begins in B2
2. "Adjacent" means "touching" a cell horizontally, vertically or diagonally
3. Zero is not positive

To count the number of positive cells adjacent to B2 (if B2 is positive),
use the following formula:

=IF(B2>0,COUNTIF(OFFSET(B2,-1,-1,3,3),">0")-1,0)

To find the count of the number of positive cells that are adjacent to a
positive cell, use this array formula:

=SUM(IF(IF(B2:F6>0,COUNTIF(OFFSET(INDIRECT("R"&ROW(INDIRECT("2:6"))&"C"&COLU
MN(INDIRECT("C2:C6",FALSE)),FALSE),-1,-1,3,3),">0")-1,0)>0,1,0))

If you'd like to find the max number of positive cells adjacent to any
positive cell in the range B2:F6, use the following array formula:

=MAX(IF(B2:F6>0,COUNTIF(OFFSET(INDIRECT("R"&ROW(INDIRECT("2:6"))&"C"&COLUMN(
INDIRECT("C2:C6",FALSE)),FALSE),-1,-1,3,3),">0")-1,0))

(Remember that you'll need to enter the array formulas with Ctrl-Shift-Enter
instead of just hitting Enter.)

/i.