formula question

[searcher]

I have a group of cells that are mostly 0. I want to display a list of the
cells that aren't 0. I was able to use this formula for the first cell:
=IF(B114>0,B114,IF(B115>0,B115,IF(B116>0,B116,IF(B117>0,B117,IF(B118>0,B118,IF(B119>0,B119,IF(B120>0,B120,IF(B121>0,B121,0))))))))

I can't figure out how to make the second and third row work.
Any help is greatly appreciated!

[davesexcel]

here's one way you could do it

Highlite the range, goto, data =>filter hit the down arrow and select custom

enter your criteria

Check out this site for filtering

http://www.contextures.com/xladvfilter01.html

[Steve]

Is there a reason you cannot use Autofilter option?
Highlight the Header Row
Click DATA - FILTER - AUTOFILTER
Now click the arrow in the Header of your choice, and select Display "Not 0"

Rgds
Steve

"searcher" wrote:

> I have a group of cells that are mostly 0. I want to display a list of the
> cells that aren't 0. I was able to use this formula for the first cell:
> =IF(B114>0,B114,IF(B115>0,B115,IF(B116>0,B116,IF(B117>0,B117,IF(B118>0,B118,IF(B119>0,B119,IF(B120>0,B120,IF(B121>0,B121,0))))))))
>
> I can't figure out how to make the second and third row work.
> Any help is greatly appreciated!

[searcher]

The cells that I want to diplay the results in are located in a separate
print area of my spreadsheet.

"searcher" wrote:

> I have a group of cells that are mostly 0. I want to display a list of the
> cells that aren't 0. I was able to use this formula for the first cell:
> =IF(B114>0,B114,IF(B115>0,B115,IF(B116>0,B116,IF(B117>0,B117,IF(B118>0,B118,IF(B119>0,B119,IF(B120>0,B120,IF(B121>0,B121,0))))))))
>
> I can't figure out how to make the second and third row work.
> Any help is greatly appreciated!

[searcher]

The cells that I want to diplay the results in are located in a separate
print area of my spreadsheet.

"searcher" wrote:

> I have a group of cells that are mostly 0. I want to display a list of the
> cells that aren't 0. I was able to use this formula for the first cell:
> =IF(B114>0,B114,IF(B115>0,B115,IF(B116>0,B116,IF(B117>0,B117,IF(B118>0,B118,IF(B119>0,B119,IF(B120>0,B120,IF(B121>0,B121,0))))))))
>
> I can't figure out how to make the second and third row work.
> Any help is greatly appreciated!

[searcher]

The cells that I want to diplay the results in are located in a separate
print area of my spreadsheet.

"searcher" wrote:

> I have a group of cells that are mostly 0. I want to display a list of the
> cells that aren't 0. I was able to use this formula for the first cell:
> =IF(B114>0,B114,IF(B115>0,B115,IF(B116>0,B116,IF(B117>0,B117,IF(B118>0,B118,IF(B119>0,B119,IF(B120>0,B120,IF(B121>0,B121,0))))))))
>
> I can't figure out how to make the second and third row work.
> Any help is greatly appreciated!

[Ron Rosenfeld]

On Fri, 10 Mar 2006 03:08:27 -0800, searcher
<[email protected]> wrote:

>I have a group of cells that are mostly 0. I want to display a list of the
>cells that aren't 0. I was able to use this formula for the first cell:
>=IF(B114>0,B114,IF(B115>0,B115,IF(B116>0,B116,IF(B117>0,B117,IF(B118>0,B118,IF(B119>0,B119,IF(B120>0,B120,IF(B121>0,B121,0))))))))
>
>I can't figure out how to make the second and third row work.
>Any help is greatly appreciated!

If your data is in the range rng, then this array formula:

=INDEX(rng,SMALL((rng<>0)*ROW(rng),ROWS($1:1)+SUM(--(rng=0))))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>. Excel
will place braces {...} around the formula.

Copy/drag down as far as required to show all the non-zero values.

The above will give a NUM error if you don't have enough non-zero entries to
fill the range. To avoid that, try this array formula:

=IF((ROWS($1:1)+SUM(--(rng=0)))>ROWS(rng),"",INDEX(rng,
SMALL((rng<>0)*ROW(rng),ROWS($1:1)+SUM(--(rng=0)))))

Note that your criteria is to "display the cells that aren't 0".

The formula above will display any cells in rng that are not 0 OR BLANK.


--ron