|
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? |
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? |
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? |
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? |
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. |
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? |
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? |
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? |
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? |
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? |
How do I get a value instead of an error or txt in an Excel ce
I understand how the ISERROR() function handles division by zero in your example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the numeral 0. However, the problem I have encountered is that numbers are downloaded as text with three trailing spaces. I have to manually remove these trailing spaces to use these numbers in the calculation. This is a time consuming task. Is there a better way to automatically convert numbers stored as text to numeric format? I suppose I could write a macro to automatically remove three characters from the end of each item in a one-dimensional array (in my case), but I've never written a macro in Excel 2007. Can the VALUE function not be used to convert text to numbers? I have had no luck using it to do so. Any help would be greatly appreciated. Best wishes, Lewis Williams "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? |
How do I get a value instead of an error or txt in an Excel ce
It's not a good way of handling DIV errors because it will hide other errors
It's better to use =IF(B1=0,0,A1/B1) You can install this macro, select the import and run the macro -- Regards, Peo Sjoblom "LHW" wrote in message ... I understand how the ISERROR() function handles division by zero in your example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the numeral 0. However, the problem I have encountered is that numbers are downloaded as text with three trailing spaces. I have to manually remove these trailing spaces to use these numbers in the calculation. This is a time consuming task. Is there a better way to automatically convert numbers stored as text to numeric format? I suppose I could write a macro to automatically remove three characters from the end of each item in a one-dimensional array (in my case), but I've never written a macro in Excel 2007. Can the VALUE function not be used to convert text to numbers? I have had no luck using it to do so. Any help would be greatly appreciated. Best wishes, Lewis Williams "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? |
How do I get a value instead of an error or txt in an Excel ce
You can install this macro, select the import and run the macro
You forgot the macro! Did you mean this one? http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... It's not a good way of handling DIV errors because it will hide other errors It's better to use =IF(B1=0,0,A1/B1) You can install this macro, select the import and run the macro -- Regards, Peo Sjoblom "LHW" wrote in message ... I understand how the ISERROR() function handles division by zero in your example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the numeral 0. However, the problem I have encountered is that numbers are downloaded as text with three trailing spaces. I have to manually remove these trailing spaces to use these numbers in the calculation. This is a time consuming task. Is there a better way to automatically convert numbers stored as text to numeric format? I suppose I could write a macro to automatically remove three characters from the end of each item in a one-dimensional array (in my case), but I've never written a macro in Excel 2007. Can the VALUE function not be used to convert text to numbers? I have had no luck using it to do so. Any help would be greatly appreciated. Best wishes, Lewis Williams "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? |
How do I get a value instead of an error or txt in an Excel ce
Thanks.
I seemed to have opened to reply to group windows and I pasted the link into the one that I closed <g -- Regards, Peo Sjoblom "T. Valko" wrote in message ... You can install this macro, select the import and run the macro You forgot the macro! Did you mean this one? http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... It's not a good way of handling DIV errors because it will hide other errors It's better to use =IF(B1=0,0,A1/B1) You can install this macro, select the import and run the macro -- Regards, Peo Sjoblom "LHW" wrote in message ... I understand how the ISERROR() function handles division by zero in your example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the numeral 0. However, the problem I have encountered is that numbers are downloaded as text with three trailing spaces. I have to manually remove these trailing spaces to use these numbers in the calculation. This is a time consuming task. Is there a better way to automatically convert numbers stored as text to numeric format? I suppose I could write a macro to automatically remove three characters from the end of each item in a one-dimensional array (in my case), but I've never written a macro in Excel 2007. Can the VALUE function not be used to convert text to numbers? I have had no luck using it to do so. Any help would be greatly appreciated. Best wishes, Lewis Williams "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? |
How do I get a value instead of an error or txt in an Excel ce
"T. Valko" wrote: You can install this macro, select the import and run the macro You forgot the macro! Did you mean this one? http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... It's not a good way of handling DIV errors because it will hide other errors It's better to use =IF(B1=0,0,A1/B1) You can install this macro, select the import and run the macro -- Regards, Peo Sjoblom "LHW" wrote in message ... I understand how the ISERROR() function handles division by zero in your example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the numeral 0. However, the problem I have encountered is that numbers are downloaded as text with three trailing spaces. I have to manually remove these trailing spaces to use these numbers in the calculation. This is a time consuming task. Is there a better way to automatically convert numbers stored as text to numeric format? I suppose I could write a macro to automatically remove three characters from the end of each item in a one-dimensional array (in my case), but I've never written a macro in Excel 2007. Can the VALUE function not be used to convert text to numbers? I have had no luck using it to do so. Any help would be greatly appreciated. Best wishes, Lewis Williams "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? |
How do I get a value instead of an error or txt in an Excel ce
Hi,
1. First let me simplify one of the formulas =IF(B1,A1/B1,0) 2. If the cells really contain trailing spaces you can remove all of them with a single command - highlight all the offending cells and press Ctrl+H, enter three spaces in the Find what box and leave the Replace with box empty. Then click Replace all. The cells will still be text most likely, but they will not contain 3 trailing spaces. 3. You can use the VALUE function to convert text numbers followed by trailing spaces to numbers =VALUE(A1) However, this might not work if the trailing spaces were not really spacebar spaces. In which case you could try =VALUE(TRIM(CLEAN(A1))) 4. However, maybe the following solution will be best: Problem: When numbers are enter as text they may not calculate within formulas as they should. A few formulas will work fine despite the numbers being entered as text. Numbers can be stored as text by 1. preformatting the cell to Text and entering the number, 2. Typing an apostrophy in front of the number '123, 3. Because the data was downloaded from a soure inwhich it was stored as a text number, 4. Because you used the Text to Columns command and converted it to text., and.... There is no sure indicator that a number is stored as text, although numbers are usually right aligned and text left aligned, this may not be the case. If you are using a later version of Excel, Error Checking green triangles may appear at the top left corner of these cell, but this feature may be off or the version of Excel may not support it. (2000 and earlier). You can find out what data type the entries are by using the =ISTEXT(A1) or =ISNUMBER(A1) functions. You can not tell by checking the Format. If a number was entered in a cell preformatted as General or as a number, then it will be a number, even if it's current format is Text. Likewise a number entered in a cell preformatted as Text will be text even if it's current format is Number, General, Date, Currency and the like. Solution: 1. Change the format to one that is numeric and then reenter the numbers (too slow and error prone.) 2. Select the cells and open the Error Checking options and choose Convert to Numbers. 3. Select an empty cell and copy it. Select the text number cell and choose Edit, Paste Special, Add (or Subtract). This method is ~100 times faster than #2. Dates are numbers, and if they are stored as text, you will not get an Error Checking triangle, so method #3 is obligatory if there is a substantial number of dates to convert. If this information is helpful, please click the Yes button. Cheers, Shane Devenshire "lypolintan74" wrote in message ... "T. Valko" wrote: You can install this macro, select the import and run the macro You forgot the macro! Did you mean this one? http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... It's not a good way of handling DIV errors because it will hide other errors It's better to use =IF(B1=0,0,A1/B1) You can install this macro, select the import and run the macro -- Regards, Peo Sjoblom "LHW" wrote in message ... I understand how the ISERROR() function handles division by zero in your example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the numeral 0. However, the problem I have encountered is that numbers are downloaded as text with three trailing spaces. I have to manually remove these trailing spaces to use these numbers in the calculation. This is a time consuming task. Is there a better way to automatically convert numbers stored as text to numeric format? I suppose I could write a macro to automatically remove three characters from the end of each item in a one-dimensional array (in my case), but I've never written a macro in Excel 2007. Can the VALUE function not be used to convert text to numbers? I have had no luck using it to do so. Any help would be greatly appreciated. Best wishes, Lewis Williams "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? |
How do I get a value instead of an error or txt in an Excel ce
Thanks to both Peo Sjoblom and Biff ,
I finally figured out how to run the macro, and it works just fine. Best wishes, Lewis Williams "T. Valko" wrote: You can install this macro, select the import and run the macro You forgot the macro! Did you mean this one? http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... It's not a good way of handling DIV errors because it will hide other errors It's better to use =IF(B1=0,0,A1/B1) You can install this macro, select the import and run the macro -- Regards, Peo Sjoblom "LHW" wrote in message ... I understand how the ISERROR() function handles division by zero in your example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the numeral 0. However, the problem I have encountered is that numbers are downloaded as text with three trailing spaces. I have to manually remove these trailing spaces to use these numbers in the calculation. This is a time consuming task. Is there a better way to automatically convert numbers stored as text to numeric format? I suppose I could write a macro to automatically remove three characters from the end of each item in a one-dimensional array (in my case), but I've never written a macro in Excel 2007. Can the VALUE function not be used to convert text to numbers? I have had no luck using it to do so. Any help would be greatly appreciated. Best wishes, Lewis Williams "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? |
How do I get a value instead of an error or txt in an Excel cell?
Why does this formula return a #value =SUM(IF(A3:A456=" S Texas",C3:C456)) The values in the a column are Words and c is amounts. This formula works on a different location {=SUM(IF(A3:A456="Houston",C3:C456)) |
How do I get a value instead of an error or txt in an Excel cell?
Have you confirmed the formula with CTRL+SHIFT+ENTER not just ENTER so that { } brackets auto appear around the formula? Also, you can use this instead: =SUMIF(A3:A456," S Texas",C3:C456) and only use ENTER to confirm as normal. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126531 |
How do I get a value instead of an error or txt in an Excel ce
Hello,
I am sort of new to Excel 2007. I am having trouble trying to get the "total" of multiplying currency with quantity. I don't really understand the help box or the answers on the message boards. What I need is someone to show me an example, that way I can get this taken care of. In disparate need of help. Rob "NBVC" wrote: Have you confirmed the formula with CTRL+SHIFT+ENTER not just ENTER so that { } brackets auto appear around the formula? Also, you can use this instead: =SUMIF(A3:A456," S Texas",C3:C456) and only use ENTER to confirm as normal. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126531 |
How do I get a value instead of an error or txt in an Excel ce
Hi I am trying to figure out a formula that allow me to avoid sign
carachters such "#< $ % &" being enter as data what can I do? Help me! "Shane Devenshire" wrote: Hi, 1. First let me simplify one of the formulas =IF(B1,A1/B1,0) 2. If the cells really contain trailing spaces you can remove all of them with a single command - highlight all the offending cells and press Ctrl+H, enter three spaces in the Find what box and leave the Replace with box empty. Then click Replace all. The cells will still be text most likely, but they will not contain 3 trailing spaces. 3. You can use the VALUE function to convert text numbers followed by trailing spaces to numbers =VALUE(A1) However, this might not work if the trailing spaces were not really spacebar spaces. In which case you could try =VALUE(TRIM(CLEAN(A1))) 4. However, maybe the following solution will be best: Problem: When numbers are enter as text they may not calculate within formulas as they should. A few formulas will work fine despite the numbers being entered as text. Numbers can be stored as text by 1. preformatting the cell to Text and entering the number, 2. Typing an apostrophy in front of the number '123, 3. Because the data was downloaded from a soure inwhich it was stored as a text number, 4. Because you used the Text to Columns command and converted it to text., and.... There is no sure indicator that a number is stored as text, although numbers are usually right aligned and text left aligned, this may not be the case. If you are using a later version of Excel, Error Checking green triangles may appear at the top left corner of these cell, but this feature may be off or the version of Excel may not support it. (2000 and earlier). You can find out what data type the entries are by using the =ISTEXT(A1) or =ISNUMBER(A1) functions. You can not tell by checking the Format. If a number was entered in a cell preformatted as General or as a number, then it will be a number, even if it's current format is Text. Likewise a number entered in a cell preformatted as Text will be text even if it's current format is Number, General, Date, Currency and the like. Solution: 1. Change the format to one that is numeric and then reenter the numbers (too slow and error prone.) 2. Select the cells and open the Error Checking options and choose Convert to Numbers. 3. Select an empty cell and copy it. Select the text number cell and choose Edit, Paste Special, Add (or Subtract). This method is ~100 times faster than #2. Dates are numbers, and if they are stored as text, you will not get an Error Checking triangle, so method #3 is obligatory if there is a substantial number of dates to convert. If this information is helpful, please click the Yes button. Cheers, Shane Devenshire "lypolintan74" wrote in message ... "T. Valko" wrote: You can install this macro, select the import and run the macro You forgot the macro! Did you mean this one? http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... It's not a good way of handling DIV errors because it will hide other errors It's better to use =IF(B1=0,0,A1/B1) You can install this macro, select the import and run the macro -- Regards, Peo Sjoblom "LHW" wrote in message ... I understand how the ISERROR() function handles division by zero in your example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to the numeral 0. However, the problem I have encountered is that numbers are downloaded as text with three trailing spaces. I have to manually remove these trailing spaces to use these numbers in the calculation. This is a time consuming task. Is there a better way to automatically convert numbers stored as text to numeric format? I suppose I could write a macro to automatically remove three characters from the end of each item in a one-dimensional array (in my case), but I've never written a macro in Excel 2007. Can the VALUE function not be used to convert text to numbers? I have had no luck using it to do so. Any help would be greatly appreciated. Best wishes, Lewis Williams "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? |
| All times are GMT +1. The time now is 02:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com