Condition to find oldest date

[Scotto811]

Hey everyone, I'm programming a spread sheet and I'm looking for the oldest date in a column with this command; =SMALL(E10:E4000,1)

It's working very nicely, but I want to only find the oldest date if the comulmn next to it is blank. The column next to it is a closed issue date, so if there is no date the issue is open. I was hoping to use the if command to have excel look at the cell next to it, if it has a date then move down to the next until it finds a blank (therefore open) cell and list that as the oldest date.

Attached a screen shot for reference. in the screen shot it would stop on the red box and provide that date 4/7/2010 because the closed issue box is empty next to it.



Uploaded with ImageShack.us

I'm using Cell 10:4000 because the number of rows changes but never goes over 4000, I also have the top 9 cells frozen so they will never be used for calculations.

Thanks for any help in advance!
Scott-

 

[Haseeb Avarakkan]

Try,

Array Formula, Confirmed with Control+Shift+Enter, not just Enter

=AVERAGE(IF(M11:M4000<>"",IF(M11:M4000 < D1,IF(U11:U4000="",D1-M11:M4000))))

 

[Scotto811]

That doesn't work, but my method is still accurate and doesnt go negative if the date goes below because of the if false command i used. I'm happy enough with that, it does its job and I can hide that column and lock it so it wont get deleted.

Thanks for the help again

 

[Aladin Akyurek]

OK, so i did this and it worked... In a separate column that I will hide, i did this formula;
=IF(M21="",FALSE,IF(U21="",IF($D$1>M21,$D$1-M21)))

and then i drag that down to 4000, just so every cell populates, then in a separate cell i did this to get the final calculation;
=SUBTOTAL(1,W11:W4000)

And that worked

Thanks for the help everyone!

Try...

Control+shift+enter, not just enter:

=AVERAGE(
    IF(U11:U4000="",
    IF(ISNUMBER(M11:M4000),
    IF(D1>M11:M4000,
      D1-M11:M4000))))