On Fri, 10 Mar 2006 03:08:27 -0800, 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(B1140,B114,IF(B1150,B115,IF(B1160,B116,IF( B1170,B117,IF(B1180,B118,IF(B1190,B119,IF(B120 0,B120,IF(B1210,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