Can you make a cell = 0 if original function is creating a"#ref!"?

[DaveinNeedofHelp]

Anyone,

Can you make a cell = 0 if original function is creating a"#ref!"?
Normally, I can manually change the refs to 0, but for large worksheets, this
is not practical.

Thanks,
Dave

 

[SGT Buckeye]

Dave, you can use the following function or something similar provided
that cell A1 is the cell in question:

=if(iserror(A1),0,original function)

This should give you the value of zero if there is an error in the cell
and should give you the resulting value if there is not an error. Hope
this helps.

 

[Pete_UK]

If you don't want to make use of a separate cell (or column), you can
amend the formula which causes the #REF error as follows:

=IF(ISERROR(existing_formula),0,existing_formula)

Hope this helps.

Pete

 

[gunnarhg]

It worked with ; in stead of , remember ;;;;;
=IF(ISERROR(existing_formula);0;existing_formula)

 

[Gord Dibben]

Depends upon your regional settings whether ; or , is accepted.


Gord Dibben MS Excel MVP

 

[UpGrade]

Depends upon your regional settings whether ; or , is accepted.


Gord Dibben MS Excel MVP

Cannot 0 be encapsulated by quotes to achieve this? "0"