How do I get a value instead of an error or txt in an Excel ce
I was using place holders because the spreadsheet is being used for external
reporting and I wanted to make it clear that they were intentionally blank.
When I left the cells completely empty, I was getting the #DIV/0 error for
having the formula refer to empty cells. So, the same initial question would
still apply since I would like to eliminate the unattractive error message
for the external reporting but I want to leave the formula there since it
will be used inn future terms.
Thanks
"Gord Dibben" wrote:
Do not place dashes in the cells. These dashes change the cell from blank to
non-blank.
Keep them empty/blank then you won't have to be concerned with them.
Why do you need place-holders?
What do they do?
A possible error trap would be =IF(cellref="-","",else whatever)
Gord Dibben MS Excel MVP
On Thu, 26 Jun 2008 14:08:03 -0700, Amy wrote:
I have a spreadsheet that i have entered a really long formula into. The
data I have now, in some parts, is empty (since I wanted to go ahead and set
up the formulas for future ease). However, since the fields are empty (I've
put dashes (-) in as place holders), I get the #VALUE! error. Is there
someway that I can do an If statement so that If I get the #VALUE! error, the
cell that contains the formula (and the error) will come up empty?
"JLatham" wrote:
"wrap" your formula inside of an IF statement with a test to see if it
creates an error or not. Consider this
A B C
1 5 0 =A1/B1
will give you a #DIV/0 error
But if you do this at C1:
=IF(ISERROR(A1/B1),"invalid",A1/B1)
you would be shown 'invalid' in C1 but if B1 is a non-zero value then you'd
get the true answer. You can substitute a value for "invalid" such as zero
or one, like:
=IF(ISERROR(A1/B1),0,A1/B1)
The ISERROR() test will handle all errors, there are other options such as
ISERR() handles any error except #N/A
while ISNA() only handles #N/A - good for use with VLookup and Hlookup
operations.
"SkiRJB" wrote:
Is there a way to get a formula to return a value instead of an error
(#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get a value
at some where other than zero?
|