Bypass errors in excel formulas

  • Thread starter Thread starter Dave R.
  • Start date Start date
D

Dave R.

You can rewrite it nearly exactly as you have described what you want (with
the exception of the second "but if not perform the function")

something like this should work (not tested throroughly!)

=+IF(ISERROR(ABS(Y24-Z24)),"",IF(ABS(Y24-Z24)<500,ABS(Y24-Z24),""))
 
i want to know if it is possible if i can bypass an error
in excel to complete an error. i'm trying to combine the
following arguements into one working arguement:

=IF(IF(ABS(A2-A4)<500,(ABS(A2-A4)),""))
and
=IF(ISERROR(ABS(A2-A4)),"",ABS(A2-A4))

i've tried many ways to combine these, but it seems like i
continue to get the #VALUE error. what i'm trying to
state is that if A2-A4 is less than 500 then do the
function if not leave it blank, but if A2-A4 emits an
error than leave it blank, but if not perform the
function. Any information will be helpful, thank you!
 
Jackson said:
i want to know if it is possible if i can bypass an error
in excel to complete an error. i'm trying to combine the
following arguements into one working arguement:

=IF(IF(ABS(A2-A4)<500,(ABS(A2-A4)),""))
and
=IF(ISERROR(ABS(A2-A4)),"",ABS(A2-A4))

i've tried many ways to combine these, but it seems like i
continue to get the #VALUE error. what i'm trying to
state is that if A2-A4 is less than 500 then do the
function if not leave it blank, but if A2-A4 emits an
error than leave it blank, but if not perform the
function. Any information will be helpful, thank you!
Click to expand...

=IF(ISERROR(ABS(A2-A4)),"",IF(ABS(A2-A4)<500,(ABS(A2-A4)),""))
 
It might be more productive to determine the reason you're getting
the #Value! error in the first place...Sometimes it's unavoidable,
but usually trying to work around an error means a flaw in design.

One reason for the #Value! error would be if A2 or A4 contained text
rather than being blank or a number. IF A2 and A4 are directly
entered, the best answer is probably to train users not to make a
cell "blank" by entering a space, but that's often a losing battle.
You can instead use data validation to require that A2 and A4 be
either numeric or truly blank.

If A2 or A4 contain a formula that returns a null string, you can
either test for that directly:

=IF(AND(A2<>"",A4<>""),IF(ABS(A2-A4)<500,ABS(A2-A4),""),"")

or , depending on what are valid inputs, you might be able to use
the SUM function, which ignores text:

=IF(ABS(SUM(A2,-A4))<500,ABS(SUM(A2,-A4),"")

The other reason the formula might return an error is that one of
the inputs returns the #VALUE! error. In that case, fixing the input
is usually better than ignoring it.

One benefit of doing one of the above is that if A2 or A4 is
calculated and returns *any* error, not just the #VALUE! error, the
error will be passed through to alert the user that something is
amiss, rather than being ignored.
 
Back
Top