IF #N/A
 

A particular formula sometimes generates an #N/A result (because of some
missing data elsewhere in the spreadsheet).

I want to be able to replace the #N/A with a more helpful error message eg :

IF((A1*A2)/A3="#N/A","Missing Data",A1/A2)

Can some SKS give me the correct syntax to do it.

TIA

Rob

=IF(ISNUMBER((A1*A2)/A3),A1/A2,"Missing Data")

Why do you change from (A1*A2)/A3 to A1/A2 - typo?

RFJ wrote:
A particular formula sometimes generates an #N/A result (because of some
missing data elsewhere in the spreadsheet).

I want to be able to replace the #N/A with a more helpful error message eg :

IF((A1*A2)/A3="#N/A","Missing Data",A1/A2)

Can some SKS give me the correct syntax to do it.

TIA

Rob

While ISNUMBER() certainly works, it will also mask other errors (e.g.,
#DIV/0, #VALUE!). For that reason I'd recommend ISNA() instead:

=IF(ISNA(A1*A2/A3),"Missing Data",A1*A2/A3)


In article ,
Aladin Akyurek wrote:

=IF(ISNUMBER((A1*A2)/A3),A1/A2,"Missing Data")

Why do you change from (A1*A2)/A3 to A1/A2 - typo?

Tx all - you pointed me in the right direction. ISERROR was even better as
it picked up a VALUE error message as well.

"JE McGimpsey" wrote in message
...
While ISNUMBER() certainly works, it will also mask other errors (e.g.,
#DIV/0, #VALUE!). For that reason I'd recommend ISNA() instead:

=IF(ISNA(A1*A2/A3),"Missing Data",A1*A2/A3)


In article ,
Aladin Akyurek wrote:

=IF(ISNUMBER((A1*A2)/A3),A1/A2,"Missing Data")

Why do you change from (A1*A2)/A3 to A1/A2 - typo?

Are you sure you *want* to pick up #VALUE!?

That often indicates a problem with logic in your sheet. I usually find
it's better NOT to mask errors, and instead prevent/fix them.


In article ,
"RFJ" wrote:

Tx all - you pointed me in the right direction. ISERROR was even better as
it picked up a VALUE error message as well.