validating references; how to recognize the #REF! condition
 

#REF!

How do you generally intercept/recognize cell reference errors? Is
there a function you can wrap another function inside

I want to leave a value of 0 whenever a #REF! would occur.

Thank you.

validating references; how to recognize the #REF! condition
 

I'm hacking on this. This seems a little verbose and requires, I
think, that the formula execute twice. But what about this:

=if(iserror(_formula_here_), 0, _formula_here_)

If that is a good solution, then the next step is to bold that 0 so
that the user knows it artificial.

validating references; how to recognize the #REF! condition
 

The most brute force method works simplest, but comes with the highest price
because EVERY formula you use it on must be run in total TWICE. The function
is IF(ISERROR() in Excel 2000-2003

=VLOOKUP(A1, AA1:AB100, 2, FALSE)

TO get the this formula to NOT error when the A1 value cannot be found, you
wrap it in an error check:

=IF(ISERROR(VLOOKUP(A1, AA1:AB100, 2, FALSE)), 0, VLOOKUP(A1, AA1:AB100, 2,
FALSE))

See how the formula has to run twice to get a value? In Excel 2007 you can
use IFERROR()

=IFERROR(VLOOKUP(A1, AA1:AB100, 2, FALSE), 0)

==========
Other formulas offer the chance to errorcheck in a less brutal way. For
instance #DIV/0 errors, you can simply check the divisor cell for a value
before running the formula in total.

=A1/B1 becomes
=IF(B1=0, 0, A1/B1)

So, it really depends on the formula you're getting the error in to
determine the best method to error check.



--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"okey" wrote:

#REF!

How do you generally intercept/recognize cell reference errors? Is
there a function you can wrap another function inside

I want to leave a value of 0 whenever a #REF! would occur.

Thank you.
.