Announcement

Collapse
No announcement yet.

Find First Value In Column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Find First Value In Column

    Hi there

    This probably is an easy one, but I couldn't find it out by myself.

    I've got time series data in columns which can have various #N/A cells until the first value appears. I am searching for a formula which retrieves the first value appearing in such a time series output list (column).

    I know this following formula to search for the last value:

    Code:
    =INDEX(M12:M500;MATCH(9.99999999999999E+307;M12:M500))
    There must be something similar to find the first value...?

    Greets, D.

  • #2
    Re: Find First Value In Column

    {=INDEX(M12:M500,MATCH(TRUE,M12:M500<>0,0))}

    array formular

    Comment


    • #3
      Re: Find First Value In Column

      Thanks for the reply!

      The formula works, but the problem with it is that I need to define an array with the same length as the data (column), so with time series data i get a few hundred cells in my array, all with the value of the first cell..

      Is there any way I can eliminate this problem, with having a non-array formula only in one cell?

      greets

      Comment


      • #4
        Re: Find First Value In Column

        Originally posted by Davidoff
        Thanks for the reply!

        The formula works, but the problem with it is that I need to define an array with the same length as the data (column), so with time series data i get a few hundred cells in my array, all with the value of the first cell..

        Is there any way I can eliminate this problem, with having a non-array formula only in one cell?

        greets

        Try...

        =INDEX(M12:500,MATCH(TRUE,INDEX(M12:M500<>0,0,0),0))



        Hope it helps!
        Last edited by Fin Fang Foom; March 6th, 2007, 00:09.

        Comment


        • #5
          Re: Find First Value In Column

          And the winner is...

          Fin Fang Foom!

          This is great, works perfectly! Thank you so much!!!

          Comment

          Working...
          X