How to replace #N/A with 0

[dmb41]

I have a sheet with formulas throughout and several " #N/A ". Is there a way to replace all these #N/As with 0 or something else with out manually replacing all.

 

[Zack Barresse]

Hi there,

Sure there is, but are you sure you want to do that? I'm assuming these are formulas which have returned an #N/A error. ?? If so, do you want to replace the actual formula with a value of 0? If your data changes, you would not have a formula there anymore. It would probably be better to use error trapping in your formula with an IF() function.

Example:
=IF(ISNA(yourformulahere),0,yourformulahere)

If you're using 2007 or later, you can make use of the IFERROR() function.

Example:
=IFERROR(yourformulahere,0)

If you just want to replace all #N/A errors of a formula result, you'll need to do it either via hand or use VBA, as the Replace functionality won't work on formula results. If you have it as text, just use the Replace functionality (Ctrl + H). If you have a formula return (with the NA() function) you'll need to do it manually or use VBA, as the Replace functionality won't work on these formula results either.

I would recommend, if you have formulas returning the N/A error, you utilize the above type(s) of error trapping for your formulas. You only need to do this once.

 

[dmb41]

Thanks! This put me in the right direction.

 

[huyle2103]

How to replace #N/A in Excel with 0 or blank cell with 3 ways

https://www.youtube.com/watch?v=rInFQZsKLK0