Announcement

Collapse
No announcement yet.

Return Earliest Date Without Corresponding Blank Cell

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

  • 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, 07:00.

  • #2
    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
    Regards.
    Daniel

    Comment


    • #3
      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

      Comment


      • #4
        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
        Regards.
        Daniel

        Comment


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

          Why not simply sort?

          Comment


          • #6


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

            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

            Comment

            Working...
            X