How do I get a value instead of an error or txt in an Excel cell?

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?

"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?

Got it, thanks. I just had to use a cell reference with the value I wanted
so the graph would show it crossing at the average I have set it at.

"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?

Glad to have helped some, and thanks for the feedback.

"SkiRJB" wrote:

> Got it, thanks. I just had to use a cell reference with the value I wanted
> so the graph would show it crossing at the average I have set it at.
>
> "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?

Helped me too. Thanks.

I used the IF Statement to get rid of the Error and substituted a period.

Saved me a lot of time and headache. Literally.

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?

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?

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?
>
>

please also help me: i use this formula:

>> =SUM(IF(ISERROR(H449:H508),0,H449:H508))

and i believe that i have it right but it would just display a zero value
("0"). i tried to evaluate the function it would give me the right answer
but just don't display in the worksheet.

1 LFKNA Cosmetics - Liquid Foundation - Nude Amber 195.00 195.00
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A

GROSS AMOUNT Php- <<< not displaying the answer which is supposed to
be "195.00"


"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?

> =SUM(IF(ISERROR(H449:H508),0,H449:H508))

That is an array formula and needs to be entered using the key combination
of CTRL, SHIFT, ENTER, not just ENTER.

Try this non-array alternative (normally entered):

=SUMIF(H449:H508,"<1E100")


--
Biff
Microsoft Excel MVP


"stylissimo" > wrote in message
...
> please also help me: i use this formula:
>
>>> =SUM(IF(ISERROR(H449:H508),0,H449:H508))
>
> and i believe that i have it right but it would just display a zero value
> ("0"). i tried to evaluate the function it would give me the right answer
> but just don't display in the worksheet.
>
> 1 LFKNA Cosmetics - Liquid Foundation - Nude Amber 195.00 195.00
> #N/A #N/A #N/A
> #N/A #N/A #N/A
> #N/A #N/A #N/A
>
> GROSS AMOUNT Php- <<< not displaying the answer which is supposed to
> be "195.00"
>
>
> "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?