Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Format N/A #DIV/O!
I would like to know if there is a custom format that will hide formula errors. I'm not talking about conditional formatting. Example: This will hide Zero's 0;-0;;@ This will hide the The Bignum (E+307) [=9.99999999999999E+307]"";General But what will hide #N/A and #DIV/O! What do you think*?* -- vane0326 ------------------------------------------------------------------------ vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731 View this thread: http://www.excelforum.com/showthread...hreadid=562310 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Format N/A #DIV/O!
"vane0326" wrote in
message ... I would like to know if there is a custom format that will hide formula errors. I'm not talking about conditional formatting. Example: This will hide Zero's 0;-0;;@ This will hide the The Bignum (E+307) [=9.99999999999999E+307]"";General But what will hide #N/A and #DIV/O! What do you think*?* -- vane0326 Hi! I'm gonna take a guess and say that is not possible. Formats are for numbers and text. Those error types are neither. Errors such as those mentioned are LOGICAL values. They are not numbers and they are not text. Biff |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Format N/A #DIV/O!
You can't do it without using conditional formatting.
OTOH, it's generally poor practice to have errors hidden in your worksheet. It's nearly always better to trap the errors instead. For instance instead of =A1/B1 where B1 could be blank or 0, use =IF(B1<0, A1/B1,"") or for #N/As, instead of =VLOOKUP(A1,J:K,2,FALSE) where A1 may not be found in J:J, use =IF(ISNA(MATCH(A1,J:J,FALSE)),"",VLOOKUP(A1,J:K,2, FALSE)) That way real errors (i.e., that you don't "expect") don't get lost because your user is used to seeing the errors elsewhere. In article , vane0326 wrote: I would like to know if there is a custom format that will hide formula errors. I'm not talking about conditional formatting. Example: This will hide Zero's 0;-0;;@ This will hide the The Bignum (E+307) [=9.99999999999999E+307]"";General But what will hide #N/A and #DIV/O! What do you think*?* |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Format N/A #DIV/O!
Yes that true JE McGimpsey what you say but when you have use =IF(ISNA(MATCH(A1,J:J,FALSE)),"",VLOOKUP(A1,J:K,2, FALSE)) in a couple thousands rows the calculations takes a long time. Oh well I thought there is a custom format for that. Thanks guys. -- vane0326 ------------------------------------------------------------------------ vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731 View this thread: http://www.excelforum.com/showthread...hreadid=562310 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding custom format | Excel Discussion (Misc queries) | |||
Need Custom format, not a formula | Excel Discussion (Misc queries) | |||
Custom Cell Format | Excel Discussion (Misc queries) | |||
Custom Number Format Text | Excel Discussion (Misc queries) | |||
Custom Time Format doesn't work for me | Excel Discussion (Misc queries) |