Home 
Search 
Today's Posts 
#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? 
#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? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
resetting last cell  Excel Discussion (Misc queries)  
missing cell borders in Excel  Excel Discussion (Misc queries)  
Possible Lookup Table  Excel Worksheet Functions  
Copy cell format to cell on another worksht and update automatical  Excel Worksheet Functions  
Paste rows of numbers from Word into single Excel cell  Excel Discussion (Misc queries) 