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]

Hi, Try,

=COUNTIFS(F10:F4000,"",E10:E4000,"<"&D1-250)

 

[Scotto811]

Awesome! That worked. thanks a bunch. I was using just the =if command with bad luck.. I'm not familiar with =countifs but i better go start reading!

thanks again everyone!

 

[Scotto811]

ok... last one i promise.

If i wanted to use the same string to average the dates, but only if the cell was blank like in the previous strings why wouldn't this work?

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

 

[Scotto811]

I've updated the cells, thats why it is now U and M instead of F and E... sorry, forgot to add that in there.

 

[Marcelo Branco]

If i understood correctly, maybe this formula

=SUMPRODUCT(--(E10:E4000<>""),--($D$1-250>=E10:E4000),--(F10:F4000=""))

M.

 

[Marcelo Branco]

ooops

I hadnt seen the last posts

M.

 

[Marcelo Branco]

To calculate the average

=AVERAGEIFS(M11:M4000,M11:M4000,"<>",U11:U4000,"=")

M.

 

[Scotto811]

No love on that one

 

[Marcelo Branco]

No love on that one

hmm...are you talking about the Averageifs formula?

where are now your data?
Column U = ?
Column M = ?

What the formula produced? An error?

M.

 

[Scotto811]

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!