IF #N/A

[RFJ]

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

 

[Aladin Akyurek]

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

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

 

[JE McGimpsey]

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)

 

[RFJ]

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]

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.