I am a complete novice with Excel, I am using Excel 2002 with windows xp
(home). I have list of numbers, some zero some none zero, I am trying to
extract the non-zero numbers and store them in sequential cells (i.e. if a10
then store it in x1,
if a20 then store it in x2, etc) but can't figure out how to get the cell to
iterate. There doesn't appear to be a "For" function ("for x=1 to 10, ...,
next") and you can only "nest" 7 "if" functions (which seems a terribly
cumbersome way of doing it anyhow). Any suggestions (please keep them simple
- I ain't all that bright) - thanks

Put this in X1 and drag down to the same length as your column of numbers.

=IF(A1<0,A1,"")

I

"Ausseshooter" wrote:

I am a complete novice with Excel, I am using Excel 2002 with windows xp
(home). I have list of numbers, some zero some none zero, I am trying to
extract the non-zero numbers and store them in sequential cells (i.e. if a10
then store it in x1,
if a20 then store it in x2, etc) but can't figure out how to get the cell to
iterate. There doesn't appear to be a "For" function ("for x=1 to 10, ...,
next") and you can only "nest" 7 "if" functions (which seems a terribly
cumbersome way of doing it anyhow). Any suggestions (please keep them simple
- I ain't all that bright) - thanks

Two other ideas:

1) Use Data/Filter/Autofilter on Column A (insert a row and add a header to
column A before turning on Autofilter). Click the drop down arrow and select
Custom, does not equal 0. Then copy/paste the results of the filter to x1.

2) Assuming your data is in A1:A8 (modify as needed), enter in x1:

=IF(ROWS(X$1:X1)COUNTIF(A$1:A$8,"<0")-COUNTBLANK(A$1:A$8),"",INDEX(A$1:A$8,SMALL(IF(A$1: A$8<0*ISNUMBER(A$1:A$8),ROW(INDIRECT("1:"&ROWS(A$ 1:A$8)))),ROWS(X$1:X1))))

But it must be entered with Cntrl+Shift+Enter (or you'll get #NUM when you
copy). Then copy it down column x as far as needed.



"Ausseshooter" wrote:

I am a complete novice with Excel, I am using Excel 2002 with windows xp
(home). I have list of numbers, some zero some none zero, I am trying to
extract the non-zero numbers and store them in sequential cells (i.e. if a10
then store it in x1,
if a20 then store it in x2, etc) but can't figure out how to get the cell to
iterate. There doesn't appear to be a "For" function ("for x=1 to 10, ...,
next") and you can only "nest" 7 "if" functions (which seems a terribly
cumbersome way of doing it anyhow). Any suggestions (please keep them simple
- I ain't all that bright) - thanks

I would suggest DataFilterAutofilterCustom

greater than 0

Copt the results to somewhere that is not affected by the filter.

Non-zero data will be copied leaving no gaps.


Gord Dibben MS Excel MVP

On Sat, 3 Feb 2007 12:03:00 -0800, Ausseshooter
wrote:

I am a complete novice with Excel, I am using Excel 2002 with windows xp
(home). I have list of numbers, some zero some none zero, I am trying to
extract the non-zero numbers and store them in sequential cells (i.e. if a10
then store it in x1,
if a20 then store it in x2, etc) but can't figure out how to get the cell to
iterate. There doesn't appear to be a "For" function ("for x=1 to 10, ...,
next") and you can only "nest" 7 "if" functions (which seems a terribly
cumbersome way of doing it anyhow). Any suggestions (please keep them simple
- I ain't all that bright) - thanks

Thanks fella's - much obliged

"Ausseshooter" wrote:

I am a complete novice with Excel, I am using Excel 2002 with windows xp
(home). I have list of numbers, some zero some none zero, I am trying to
extract the non-zero numbers and store them in sequential cells (i.e. if a10
then store it in x1,
if a20 then store it in x2, etc) but can't figure out how to get the cell to
iterate. There doesn't appear to be a "For" function ("for x=1 to 10, ...,
next") and you can only "nest" 7 "if" functions (which seems a terribly
cumbersome way of doing it anyhow). Any suggestions (please keep them simple
- I ain't all that bright) - thanks