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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
It worked with ; in stead of , remember ;;;;;
=IF(ISERROR(existing_formula);0;existing_formula)
 
Depends upon your regional settings whether ; or , is accepted.


Gord Dibben MS Excel MVP
 
Back
Top