A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old September 20th 06, 12:29 AM posted to microsoft.public.excel.misc
SkiRJB
external usenet poster
 
Posts: 2
Default 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?
Ads
  #2  
Old September 20th 06, 12:44 AM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 3,366
Default How do I get a value instead of an error or txt in an Excel cell?

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

  #3  
Old September 20th 06, 12:48 AM posted to microsoft.public.excel.misc
SkiRJB
external usenet poster
 
Posts: 2
Default How do I get a value instead of an error or txt in an Excel ce

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?

  #4  
Old September 20th 06, 02:05 AM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 3,366
Default How do I get a value instead of an error or txt in an Excel ce

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?

  #5  
Old May 13th 08, 07:46 AM posted to microsoft.public.excel.misc
Magna Carta
external usenet poster
 
Posts: 1
Default How do I get a value instead of an error or txt in an Excel ce


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.
  #6  
Old June 26th 08, 10:08 PM posted to microsoft.public.excel.misc
Amy
external usenet poster
 
Posts: 165
Default How do I get a value instead of an error or txt in an Excel ce

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?

  #7  
Old June 28th 08, 12:23 AM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,911
Default How do I get a value instead of an error or txt in an Excel ce

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?


  #8  
Old June 28th 08, 12:52 AM posted to microsoft.public.excel.misc
Amy
external usenet poster
 
Posts: 165
Default 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?

>
>

  #9  
Old July 30th 08, 02:30 AM posted to microsoft.public.excel.misc
stylissimo
external usenet poster
 
Posts: 8
Default How do I get a value instead of an error or txt in an Excel ce

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?

  #10  
Old July 30th 08, 03:37 AM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,768
Default How do I get a value instead of an error or txt in an Excel ce

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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
resetting last cell jagdish.eashwar Excel Discussion (Misc queries) 11 March 31st 06 02:06 AM
missing cell borders in Excel Nak Excel Discussion (Misc queries) 7 March 9th 06 04:54 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
Paste rows of numbers from Word into single Excel cell BecG Excel Discussion (Misc queries) 1 December 8th 04 05:55 PM


All times are GMT +1. The time now is 04:01 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.