Custom Format N/A #DIV/O!

[vane0326]

I would like to know if there is a custom format that will hide formul
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*?

 

[Biff]

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*?*

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

 

[JE McGimpsey]

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.

 

[vane0326]

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.