Answered by:
Find first nonzero value in column

Hi,
Using Excel 97. I have a long list of number values and I'd like to return the first nonzero value. I searched on Google, and came up with this formula:
=INDEX(E2:E16390,SMALL(IF(E2:E16390>0,COLUMN(E2:E16390)),1),1)
which I have entered using CTRL+SHIFT+ENTER
The first nonzero value is actuaslly on row 707, but the above formula comes back with "0". The value in cell E707 is actually "71" so I'd like that to be returned.
Thanks in advance,
Alain
Question
Answers

On Thu, 22 Sep 2011 12:13:37 +0000, Alain Dekker wrote:>Thanks. When I try you;re formula:>>=INDEX(E2:E16390,MATCH(TRUE,E2:E16390<>0,0))>>and then use the CTRL+SHIRT+ENTER to enter the forumla, I get the weird value of "1.385869565".>Not sure what I'm doing wrong here. What is "**arrayentered**"? Isn't my range "E2:E16390" what is required here?>>Thanks,>AlainArrayentered means to enter the formula as an array formula. As I wrote, if you do that correctly, Excel will place braces {...} around your formula.Your range references are correctThe value 1.385869565 is likely someplace between E2:E707 (where you wrote you thought the first nonzero value was located).For troubleshooting, try the following arrayentered formula:=MATCH(TRUE,E2:E16390<>0,0)It should return the row number that it is matching. Then select that row in Column E, and see what is in the Formula Bar.
Ron Marked as answer by William Zhou CHNModerator Monday, October 03, 2011 7:18 AM
All replies

On Thu, 22 Sep 2011 11:59:22 +0000, Alain Dekker wrote:>>>Hi,>>Using Excel 97. I have a long list of number values and I'd like to return the first nonzero value. I searched on Google, and came up with this formula:>>=INDEX(E2:E16390,SMALL(IF(E2:E16390>0,COLUMN(E2:E16390)),1),1)>>which I have entered using CTRL+SHIFT+ENTER>>The first nonzero value is actuaslly on row 707, but the above formula comes back with "0". The value in cell E707 is actually "71" so I'd like that to be returned.>>Thanks in advance,>AlainThis formula must be **arrayentered**:=INDEX(MyRange,MATCH(TRUE,MyRange<>0,0))To **arrayenter** a formula, after enteringthe formula into the cell or formula bar, hold down<ctrl><shift> while hitting <enter>. If you did thiscorrectly, Excel will place braces {...} around the formula.
Ron 
Thanks. When I try you;re formula:
=INDEX(E2:E16390,MATCH(TRUE,E2:E16390<>0,0))
and then use the CTRL+SHIRT+ENTER to enter the forumla, I get the weird value of "1.385869565".
Not sure what I'm doing wrong here. What is "**arrayentered**"? Isn't my range "E2:E16390" what is required here?Thanks,
Alain<Ron Rosenfeld> wrote in message news:9ecea888d7b64edbbf2c1013ba8475eb@communitybridge.codeplex.com...
On Thu, 22 Sep 2011 11:59:22 +0000, Alain Dekker wrote:
Hi,
Using Excel 97. I have a long list of number values and I'd like to return the first nonzero value. I searched on Google, and came up with this formula:
=INDEX(E2:E16390,SMALL(IF(E2:E16390>0,COLUMN(E2:E16390)),1),1)
which I have entered using CTRL+SHIFT+ENTER
The first nonzero value is actuaslly on row 707, but the above formula comes back with "0". The value in cell E707 is actually "71" so I'd like that to be returned.
Thanks in advance,
AlainThis formula must be **arrayentered**:
=INDEX(MyRange,MATCH(TRUE,MyRange<>0,0))

To *arrayenter* a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
Ron

On Thu, 22 Sep 2011 12:13:37 +0000, Alain Dekker wrote:>Thanks. When I try you;re formula:>>=INDEX(E2:E16390,MATCH(TRUE,E2:E16390<>0,0))>>and then use the CTRL+SHIRT+ENTER to enter the forumla, I get the weird value of "1.385869565".>Not sure what I'm doing wrong here. What is "**arrayentered**"? Isn't my range "E2:E16390" what is required here?>>Thanks,>AlainArrayentered means to enter the formula as an array formula. As I wrote, if you do that correctly, Excel will place braces {...} around your formula.Your range references are correctThe value 1.385869565 is likely someplace between E2:E707 (where you wrote you thought the first nonzero value was located).For troubleshooting, try the following arrayentered formula:=MATCH(TRUE,E2:E16390<>0,0)It should return the row number that it is matching. Then select that row in Column E, and see what is in the Formula Bar.
Ron Marked as answer by William Zhou CHNModerator Monday, October 03, 2011 7:18 AM

Thanks! I was pretty sure I was using CTRL+SHIFT+ENTER (to get Excel to put the baces around the formula) but your troubleshooting helped solve the problem. By running your formula first, then building it slowly up I got it to work:
=INDEX(E2:E16390,MATCH(TRUE,E2:E16390<>0,0))
I then put the cursor in the Formular Bar and pressed CTRL+SHIFT+ENTER and it works.
Thanks again,
Alain 