Time for IFERROR?

[PAL]

I am calculating the time between two dates. In the formula, I account for
the dates being reversed by a conditional. Sometimes the data points are
"N/A" if there shouldn't be a date. The formula below returns a "#VALUE!"
error. Is there a way using IFERROR to return a blank?

=IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)>0,('Raw Data'!F3-'Raw
Data'!E3)/$J$1,"")

Thanks.

 

[Tyro]

=IFERROR(IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)>0,('Raw Data'!F3-'Raw
Data'!E3)/$J$1,""),"")

Tyro

 

[PAL]

Tyro,

It didn't work. It came back with "#Name?"

 

[Bob Phillips]

Do you mean a missing date

=IF(OR('Raw Data'!E3="",'Raw Data'!F3=""),"",N(ABS('Raw Data'!F3-'Raw
Data'!E3))/$J$1)

or a date of #NA()

=IF(OR(ISNA('Raw Data'!E3),ISNA('Raw Data'!F3)),"",N(ABS('Raw Data'!F3-'Raw
Data'!E3))/$J$1)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[David Biddulph]

Which version of Excel do you have? Tyro had presumably assumed from your
reference to IFERROR that you were using Excel 2007.
If not, the nearest equivalent you'll get is ISERROR, which needs the longer
construct of =IF(ISERROR(your_formula),"",your_formula).

 

[T. Valko]

Maybe this:

=IF(COUNT('Raw Data'!E3:F3,$J$1)<3,"",('Raw Data'!F3-'Raw Data'!E3)/$J$1)

 

[PAL]

Hi Bob,

I did mean "N/A", but not the excel error version(#N/A). Users will not
understand the "#" symbol. In reality instead of a date, it should be
considered text and I assume therein lies the problem. It does work if I
include the "#". However, those cells with real dates get the "#REF"error.

 

[Bob Phillips]

=IF(COUNTIF('Raw Data'!E3:F3,"N/A")>0,"",('Raw Data'!F3-'Raw Data'!E3)/$J$1)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)