Return non-zero cells in column

[Greg]

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

 

[Peo Sjoblom]

You could use data>autofilter, select 0 from dropdown, select and press F5,
click special and visible cell only, then delete the 0s. If you need to keep
them, from dropdown autofilter select custom, select rows that are not equal
to 0, then repeat and select visible cells only, copy and paste them
somewhere else and there you would have a list of numbers without the 0s.
Then just do something like

=H2&":"&H3

and copy down, if there are only 2 numbers you can use this formula

=A2&":"&INDEX(A3:A20,MATCH(TRUE,A3:A20<>0,0))

entered with ctrl + shift & enter



Regards,

Peo Sjoblom

 

[Greg]

Peo,
Thanks a lot. I guess I will have to use your suggestion, if I don't get
anything better. The problem is that I can't delete rows and it's a part of
larger workbook intended for an end user. They are not going to do all those
filter operations.

I should have mentioned this before, but I just thought about it.

The non-zero cells are populated based on another column with dates (column
J) via formula =(MONTH(J3)<>MONTH(J4))*(Y3)

I am trying to calculate monthly return and column Y has daily asset value.
The problem is that some days are holidays or don't have any activity so
monthly periods are not constant.

Does this help?
Thanks again,
Greg

 

[Domenic]

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!

 

[Greg]

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]

Just for the record, replace my first formula with the following...

B1, copied down:

=N(OFFSET($A$1,SMALL(IF($A$1:$A$100<>0,ROW($A$1:$A$100)-CELL("row",B$1)),
ROW()+ROW(B2)-2),0))

C1, copied down:

=N(OFFSET($A$1,SMALL(IF($A$1:$A$100<>0,ROW($A$1:$A$100)-CELL("row",C$1)),
ROW()*2),0))

Hope this helps!