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)

"Pete_UK" wrote:

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

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


Gord Dibben MS Excel MVP

On Tue, 7 Apr 2009 09:43:01 -0700, gunnarhg
wrote:

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

"Pete_UK" wrote:

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

On Tue, 07 Apr 2009 10:48:47 -0700, Gord Dibben <gorddibbATshawDOTca
wrote:

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


Gord Dibben MS Excel MVP

On Tue, 7 Apr 2009 09:43:01 -0700, gunnarhg
wrote:

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

"Pete_UK" wrote:

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



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