Posted to microsoft.public.excel.misc
|
|
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?
|