Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validating a Not Equal To Condition | New Users to Excel | |||
validating | Excel Discussion (Misc queries) | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
validating | Excel Worksheet Functions | |||
Validating | Excel Discussion (Misc queries) |