Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Return Earliest Date Without Corresponding Blank Cell

  1. #1
    Join Date
    31st May 2007
    Posts
    9

    Return Earliest Date Without Corresponding Blank Cell

    I have a date range, containing blanks and values. I need a function to show the earliest date that contains a non-blank value. The function should be robust enough that I can sort by date and still show the correct data.

    Example...(correct answer would be 10/26/2006)

    Date ... Value
    10/23/2006
    10/24/2006
    10/25/2006 0.08
    10/26/2006 0.10
    10/27/2006
    10/28/2006
    10/29/2006 0.18
    10/30/2006 0.20
    10/31/2006
    11/01/2006
    11/02/2006
    11/03/2006 (0.18)
    11/04/2006 (0.15)

    Thank you!
    Last edited by effang; April 26th, 2008 at 07:00.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th March 2008
    Posts
    449

    Re: Date Range With Blanks, Values. Show Date With First Value

    Array formula (validate with Ctrl+Shift+Enter) :
    =MIN(IF(B1:B13<>"",1,2)*A1:A13)
    Daniel

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    31st May 2007
    Posts
    9

    Re: Date Range With Blanks, Values. Show Date With First Value

    awesome, looks good. what if the data range is constantly influx? I tried using the entire column, EG (b:b,."",12) but that didn't seem to work.

    It probably has to do with the fact that as the date column grows larger and larger, you will encounter blanks as well, which would naturally return a min of 0. Seems like an easy way is to tie out an if function, to return the row # where both date and data return 0, and plug that into the array...not sure.

    For example

    A B
    10/01/2008 data
    10/01/2008 data
    10/01/2008 data
    10/01/2008 data
    blank
    blank
    blank

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th March 2008
    Posts
    449

    Re: Date Range With Blanks, Values. Show Date With First Value

    If there are blank cells in column A, use the array formula :
    =MIN(IF(B1:B60000<>"",1,2)*IF(A1:A60000>0,A1:A15,40000))
    Moreover, you can't use entire rows or columns in array formula. You may use A1:A65535, but not A:A.
    HTH
    Daniel

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

  6. #6
    Join Date
    24th April 2007
    Location
    England
    Posts
    675

    Re: Date Range With Blanks, Values. Show Date With First Value

    Quote Originally Posted by effang
    It probably has to do with the fact that as the date column grows larger and larger, you will encounter blanks as well, which would naturally return a min of 0.
    Presumably you won't have rows where A is blank and B contains data? If so then try

    =MIN(IF(B1:B1000<>"",A1:A1000))

    confirmed with CTRL+SHIFT+ENTER

    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. Replies: 4
    Last Post: April 4th, 2008, 10:15
  2. Return Earliest Time Above Zero 00:00:00
    By GuyGadois in forum EXCEL HELP
    Replies: 5
    Last Post: November 28th, 2007, 07:53
  3. Return Corresponding Data From Earliest Date
    By HarryFrohlich in forum EXCEL HELP
    Replies: 17
    Last Post: June 1st, 2007, 19:41
  4. If....Blank cell return
    By Alec H in forum EXCEL HELP
    Replies: 2
    Last Post: February 24th, 2006, 19:35

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