software

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(B114gt;0,B114,IF(B115gt;0,B115,IF(B116gt;0,B116,IF(B 117gt;0,B117,IF(B118gt;0,B118,IF(B119gt;0,B119,IF(B120gt;0 ,B120,IF(B121gt;0,B121,0))))))))

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


here's one way you could do it

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

enter your criteria

Check out this site for filtering

www.contextures.com/xladvfilter01.html--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=520997Is 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 quot;Not 0quot;

Rgds
Steve

quot;searcherquot; wrote:

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

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

quot;searcherquot; wrote:

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

On Fri, 10 Mar 2006 03:08:27 -0800, searcher
gt; wrote:

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

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

=INDEX(rng,SMALL((rnglt;gt;0)*ROW(rng),ROWS($1:1) SUM(--(rng=0))))

To enter an array formula, hold down lt;ctrlgt;lt;shiftgt; while hitting lt;entergt;. 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)))gt;ROWS(rng),quot;quot;,INDEX(rng,
SMALL((rnglt;gt;0)*ROW(rng),ROWS($1:1) SUM(--(rng=0)))))

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

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