none
Find first non-zero value in column

    Question

  • Hi,

    Using Excel 97. I have a long list of number values and I'd like to return the first non-zero 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 non-zero 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

    Thursday, September 22, 2011 11:59 AM

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 "**array-entered**"? Isn't my range "E2:E16390" what is required here?
    >
    >Thanks,
    >Alain
     
    Array-entered 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 correct
     
    The value 1.385869565 is likely someplace between E2:E707  (where you wrote you thought the first non-zero value was located).
     
    For troubleshooting, try the following array-entered 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
    Thursday, September 22, 2011 12:50 PM

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 non-zero 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 non-zero 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
     
     
    This formula must be **array-entered**:
     
    =INDEX(MyRange,MATCH(TRUE,MyRange<>0,0))
     
    ----------------------------------------
     
    To **array-enter** 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
    Thursday, September 22, 2011 12:09 PM
  • 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 "**array-entered**"? Isn't my range "E2:E16390" what is required here?

    Thanks,
    Alain

    <Ron Rosenfeld> wrote in message news:9ecea888-d7b6-4edb-bf2c-1013ba8475eb@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 non-zero 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 non-zero 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

    This formula must be **array-entered**:

    =INDEX(MyRange,MATCH(TRUE,MyRange<>0,0))

    ----------------------------------------

    To *array-enter* 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

    Thursday, September 22, 2011 12:13 PM
  • 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 "**array-entered**"? Isn't my range "E2:E16390" what is required here?
    >
    >Thanks,
    >Alain
     
    Array-entered 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 correct
     
    The value 1.385869565 is likely someplace between E2:E707  (where you wrote you thought the first non-zero value was located).
     
    For troubleshooting, try the following array-entered 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
    Thursday, September 22, 2011 12:50 PM
  • 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

    Thursday, September 22, 2011 1:19 PM
  • Glad to help.  Thanks for the feedback.
    Ron
    Thursday, September 22, 2011 1:28 PM