Not displaying #VALUE errors

R

Rob

Hi

I'm sure this has been asked before but I can't find it in Help or on this
forum. I have many formulas in Excel (2003) where some of them return a
#VALUE, I'm happy with this as I know there is certain data missing.
However, for presentation purposes I do not want to display #VALUE, can I
just display a blank or a zero if the result of a formula is an error???
I've looked at some of the functions available in XL but can't find
anything.

Any help will be appreciated.

Thanks

Rob
 
L

Leo Heuser

Hi Rob

One way:

=IF(ISERROR(Formula),"-",Formula)

Will display a hyphen, if the formula returns an error.
 
J

JulieD

Hi Rob

the normal way to deal with this is to wrap the formula that can return the
#VALUE error in an IF formula that displays something else
e.g.
=IF(ISERROR(A1*B1),0,A1*B1)
or
=IF(ISERROR(A1*B1),"",A1*B1)
 
R

Rob

Thanks guys!

JulieD said:
Hi Rob

the normal way to deal with this is to wrap the formula that can return
the #VALUE error in an IF formula that displays something else
e.g.
=IF(ISERROR(A1*B1),0,A1*B1)
or
=IF(ISERROR(A1*B1),"",A1*B1)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
Click to expand...
 
R

Ron Rosenfeld

Hi

I'm sure this has been asked before but I can't find it in Help or on this
forum. I have many formulas in Excel (2003) where some of them return a
#VALUE, I'm happy with this as I know there is certain data missing.
However, for presentation purposes I do not want to display #VALUE, can I
just display a blank or a zero if the result of a formula is an error???
I've looked at some of the functions available in XL but can't find
anything.

Any help will be appreciated.

Thanks

Rob
Click to expand...

You could use conditional formatting; and format the font to the background
color (nominally white) if there is an error.


--ron
 
Top