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. 


Thread Tools  Display Modes 
#1




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




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




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




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




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




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




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
nonblank. Keep them empty/blank then you won't have to be concerned with them. Why do you need placeholders? 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 nonzero 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




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 > nonblank. > > Keep them empty/blank then you won't have to be concerned with them. > > Why do you need placeholders? > > 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 nonzero 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




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




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 nonarray 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 nonzero 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  


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 03: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 04:55 PM 