Finding gaps

[Guest]

Hi!

here is the data from A1:R4

1 2 3 4 0 1 2 0 0 1 2 3 4 5 0 1 2 3
1 2 3 4 5 6 7 8 9 0 1 2 0 1 2 3 4 5
1 0 1 0 1 2 3 0 1 2 3 4 5 6 7 0 1 2
0 1 0 1 2 3 4 5 0 1 2 3 0 1 2 0 1 2
1 2 3 4 0 1 2 3 4 5 6 7 8 0 1 2 3 4

I want to first search all zeros in the range A1:R1 and put the left number
of the zero in cell S1. Then search the next zero in the same range and put
the left number of the second zero in cell T1. Do this until we are finished
with checking zeros in the range A1:R1. The result in the range S1:V1 should
be 4 2 0 5

Once finished, go to the next range B1:R1 and do this till the last range
B4:R4

Maxi

 

[Jason Morin]

Copy this into S1 and press ctrl + shift + enter:

=INDEX($A1:$Q1,SMALL(IF($B1:$R1=0,COLUMN($B1:$R1)),COLUMN
()-18)-1)

Fill the formula down to S4. Now fill S1:S4 across until
you receive 4 #NUM! errors in the column.

HTH
Jason
Atlanta, GA