Domenic,

Thanks a lot, this is some formula you designed!
Appreciate the work you put into it
Unfortunately, this won't be too useful, since it is using a few volatile
functions like Indirect() and row(). Taking into account that my range could
run lengths, it will slow the things considerably.

I guess I will resort to running Advanced Filter via macro, if no other
solutions are offered.
Greg


"Domenic" wrote:

I'm not sure if this will do, but see it the following helps. Assuming
that Column A contains your numbers...

1) Select B1:C1

2) With those two cells highlighted, enter the following array formula,
that needs to be confirmed with CONTROL+SHIFT+ENTER, and copy down:

=N(OFFSET($A$1,SMALL(IF($A$1:$A$100<0,ROW($A$1:$A $100)-CELL("row",$B$1))
,COLUMN(INDIRECT(CHAR(65+(ROW()*2-2))&":"&CHAR(65+(ROW()*2-2)+1)))),0))

....adjust the range accordingly.

3) D1, copied down:

=B1&":"&C1

Hope this helps!

In article ,
"Greg" wrote:

Hi,
I have a column with single numerical cells and zeros in between. See
example below.
All I need to take the ratio of the top number to next number in an adjacent
column. The problem is that rows with zeros between numbers vary, but # zero
rows is always greater than 5 and less than 30. I would like a formula
solution if possible.

Thanks a bunch.

1151
0
0
0
0
0
0
6262
0
0
0
0
0
0
0