Using IFERROR in conjunction with array formula

[Wookiee]

I currently have a spreadsheet which examines a range of cells (date formatted) and determines the oldest and newest dates using MAX and MIN formulas. However, I've recently revised the method by which the source data cells determine the date values so that they utilize array formulas. Previously, they used a VLOOKUP formula so that a dash appears in the cell if no date value was returned. It went something like this:

=IFERROR(VLOOKUP($A20,wsSource!$A:$C, 3, 0), "-")

I changed the formula to an array formula, however now I'm unable to figure out how to produce the dash value if the cell with the lookup data does not return a value. Now I have a bunch of "1/0/00" cells whereas they used to simply reflect dashes. And that wreaks havoc with my MIN and MAX formulas in another part of the spreadsheet.

So I can conceive of two different methods to solve my problem, but I've been unable to figure out how to achieve my desired results.

The first thought I had was to try and combine IFERROR (or IF arrayresult = 0) so that it would display a dash. But I couldn't construct a formula without Excel throwing an error message. I've seen cases where an array could be manually keyed in the middle of a formula without the CTL+SHIFT+ENTER, but I couldn't replicate that function.

The other thought that occurred to me was to revise my MIN formula so that it ignores any values of zero. Currently my MIN formula is:

=IF(MIN(F19:F37)=0, "-", MIN(F19:F37))

As I was composing this, it occurred to me that I could add an extra bit of VBA code to this process to loop through the results and switch all date values of 0 to the dash character, but I would prefer to be able to use a formula. Can anyone suggest an efficient formula for this situation?

 

[Wookiee]

Here is Dunn's code for the V() function. It allows one to avoid performace degradation due repeating complex calculations in a formula

Excellent! I've always hated having to include redundant calculations in my formulas. And this should make my VBA code much less cumbersome, as well. Thanks for all your help. I love this forum!

 

[Aladin Akyurek]

Excellent! I've always hated having to include redundant calculations in my formulas. And this should make my VBA code much less cumbersome, as well. Thanks for all your help. I love this forum!

Great. Thanks for providing feedback. And a happy new year.