Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Find First Value In Column

  1. #1
    Join Date
    12th January 2007
    Posts
    17

    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:

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

    Greets, D.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    9th September 2004
    Posts
    88

    Re: Find First Value In Column

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

    array formular

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th January 2007
    Posts
    17

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th March 2005
    Posts
    507

    Re: Find First Value In Column

    Quote 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 at 00:09.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th January 2007
    Posts
    17

    Re: Find First Value In Column

    And the winner is...

    Fin Fang Foom!

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Find/Search Column Heading Text Within Column
    By f.cembrola in forum EXCEL HELP
    Replies: 1
    Last Post: June 11th, 2008, 08:22
  2. Replies: 4
    Last Post: May 24th, 2008, 03:03
  3. Replies: 12
    Last Post: August 5th, 2006, 00:24
  4. Replies: 2
    Last Post: May 12th, 2006, 11:48
  5. Replies: 4
    Last Post: December 10th, 2004, 04:42

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno