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?

 

[Andrew Poulsom]

What formula did you try that didn't work?

 

[Aladin Akyurek]

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?

Why did you ditch the VLOOKUP formula?

The MIN formula that ignores the zero values if at all possible...

=MIN(IF(ISNUMBER(F19:F37),IF(F19:F37>0,F19:f37)))

 

[Wookiee]

What formula did you try that didn't work?​

I had attempted to use this formula (based on 2 named ranges):

=IFERROR({MIN(IF(PORep=A19, POOld))}=0, "-")

 

[Aladin Akyurek]

I had attempted to use this formula (based on 2 named ranges):

=IFERROR({MIN(IF(PORep=A19, POOld))}=0, "-")

If you are trying to return a dash when conditional IF returns a 0...

=IF(ISNUMBER(1/MIN(IF(PORep=A19, POOld))),MIN(IF(PORep=A19, POOld)),"-")

Using Dunn's V() function...

=IF(V(MIN(IF(PORep=A19, POOld))),V(),"-")

The foregoing formulas need to be confirmed with control+shift+enter.

 

[Wookiee]

I apologize. I sort of combined 2 different methods when I showed my formula attempts. I put in the IFERROR and the =0. Since Excel wouldn't keep my original formula, I had to attempt to recreate it. In my haste to reply, I miskeyed my formulas. Actually, I tried:

=IFERROR({MIN(IF(PORep = A19, POOld))}, "-")

and

=IF({MIN(IF(PORep = A19, POOld))}, "-")

 

[Aladin Akyurek]

I apologize. I sort of combined 2 different methods when I showed my formula attempts. I put in the IFERROR and the =0. Since Excel wouldn't keep my original formula, I had to attempt to recreate it. In my haste to reply, I miskeyed my formulas. Actually, I tried:

=IFERROR({MIN(IF(PORep = A19, POOld))}, "-")

and

=IF({MIN(IF(PORep = A19, POOld))}, "-")

You apparently missed my two replies...

 

[Wookiee]

Although I'm not familiar with Dunn's V function, the first example formula you provided did the trick right nicely. Thank you very much!

 

[Wookiee]

Actually, I was composing a lengthy response to you which explained why I had ditched the VLOOKUP formula. Then I noticed my formula error in response to Andrew Poulsom's question and erased my response to you when I posted the corrected version of my formula attempts. Sorry, I'm not used to getting such rapid responses.

 

[Aladin Akyurek]

Although I'm not familiar with Dunn's V function, the first example formula you provided did the trick right nicely. Thank you very much!

You are welcome.

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

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

Actually, I was composing a lengthy response to you which explained why I had ditched the VLOOKUP formula. Then I noticed my formula error in response to Andrew Poulsom's question and erased my response to you when I posted the corrected version of my formula attempts. Sorry, I'm not used to getting such rapid responses.

No problem. Thanks for providing feedback.