Not displaying #VALUE errors
 

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

Hi Rob

One way:

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

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Rob" skrev i en meddelelse
...
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

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
"Rob" wrote in message
...
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

Thanks guys!

"JulieD" wrote in message
...
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
"Rob" wrote in message
...
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

You're welcome :-)

LeoH


"Rob" skrev i en meddelelse
...
Thanks guys!

On Fri, 20 May 2005 13:01:40 +0100, "Rob" wrote:

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


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


--ron