Quick way to change #N/A and #REF to 0?

[Jake_Wrestler]

I have a worksheet that has many many formulas and some come up with #N/A or #REF. I would like to convert these to a zero value. thanks!

 

[Mark W.]

I have a worksheet that has many many formulas and some come up with #N/A or #REF. I would like to convert these to a zero value. thanks!

Why are you getting a #REF! error?

 

[Iridium]

Something like

=IF(ISERROR(yourformula),0,yourformula)

 

[Jake_Wrestler]

I had some poorly defined array names (which I have since fixed)...

 

[Jake_Wrestler]

Awesome, that worked like a charm. Love that ISERROR function

 

[Mark W.]

Awesome, that worked like a charm. Love that ISERROR function

Unfortunately, the ISERROR construction has some dire consequences. It masks all error messages -- many of which are important for diagnosis. It's a much better practice to remedy the errors rather than hiding them.

 

[EdE]

You can always use ISNA or ISREF.... :wink:

 

[just_jon]

If you'd post some of the formulas which generate errors, we might be able to help get rid of some of them. But Mark is right -- a blanket cover-up of error messages can sometimes hide an underlying fault with the data or with the logic of the formula.

As an example, if you are using VLOOKUP and know, going in, that there will not always be a match (exact-type) found for your target string, you can get away with something like:

=IF(ISNUMBER(MATCH"XYZ",A1:A100,0),VLOOKUP("XYZ",A1:Z100,20,0),"")

 

[Jake_Wrestler]

wow, isna and isref make it much better. Thanks guys! (ps. Jon I would post it but the data is spotty, hence the #N/As. I fixed my #REFs. )

 

[Mark W.]

wow, isna and isref make it much better. Thanks guys! (ps. Jon I would post it but the data is spotty, hence the #N/As. I fixed my #REFs. )

However, the formula...

=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

...is very inefficient since the VLOOKUP is performed twice for those requests that return a value other than #N/A!