Adjacent cells +

M

MJSlattery

Hi:

I have 15 adjacent cells in a row and they will allways contain th
value zero with two exceptions. Two of these cell will have a value o
1.

Here is my problem. I need to return the value of 3 if they ar
adjacent cells, i.e., (0,0,0,1,1,0,0,0) the value 2 if there is on
zero cell between them, (0,0,0,0,0,1,0,1,0) and the value 1 if ther
are two zero value cells between the two cells containing ones
(0,0,0,0,0,1,0,0,1,0,0,0).

I have scoured two forums trying to find a soultion to this and nothin
comes close.

It can go into vba if necessary but would prefer to keep it in excel.

Thanks, Michae
 
C

crispbd

step 1)
Let column P have the concatenation formula of the 15 cell values:
=A1 & B1 & C1 & D1 & E1 & F1 & G1 & H1 & I1 & J1 & K1 & L1 & M1 & N1 &
O1

step 2) In column Q, use the following formula

=IF(ISERROR(FIND("11",P1,1)),IF(ISERROR(FIND("101",P1,1)),IF(ISERROR(FIND("1001",P1,1)),"",1),2),3)

This will show 3 for adjacent 1's, 2 for 101 's, and 1 for 1001's, then
you can fill the column if you need
 
Top