Number format hide errors

[opislak]

Hello intelligent people,

I'm trying out formulas in the custom number format section to prevent numbers to be shown if they are = 0, to show them in black when > 0, in red when < 0. This one almost works fine for me:

• if I want to show a result in euros (€-sign): [Black]#.##0 €;[Red]#.##0- €;;@
• if I want to show a result in mandays (MD): [Black]#.##0_- [$MD];[Red]#.##0-_-[$MD];;@

The fine thing here is that the table doesn't look overcrowded with zeroes and only shows relevant numbers in plus or minus.
However, what should I do to prevent showing errors like #DIV/0! (then the same should happen like if the result would be zero: show a blank cell).

Problem: conditional formatting is no option. We work in a multilingual company (Dutch, French, English) and Excel seems to have problems with translating formulas. Excel only translates the "regular" formulas in cells, but conditional formatting formulas are not being translated when a user of another language opens the file.

Thanks in advance for any valuable input.
Greetings, Patrick.

 

[mikerickson]

To handle #DIV/0 errors, ISERROR functions would be better than formatting, to stop the error from causing further errors from subsequent calculation.

 

[ISY]

Hi,

.FormatConditions.Add (error)

 

[opislak]

That's right, also did that temporarily in my formula. I just wondered if it would be possible in the number format. (I like to learn )
But thanks!

 

[opislak]

Hi,

.FormatConditions.Add (error)

Sorry, I'm not sure I understand you. If you mean this, it doesn't work:
[Black]#.##0,00_- [$MD];[Red]#.##0,00-_-[$MD];;@.FormatConditions.Add(#DIV/0!)

 

[ISY]

Hi

 

[Fluff]

@ISY
The OP has already said that conditional formatting is not an option.

 

[opislak]

To handle #DIV/0 errors, ISERROR functions would be better than formatting, to stop the error from causing further errors from subsequent calculation.

I did use the errorhandling first in the formula and this works fine. Thus the rest follows.
Thanks all for the assistance!
Grts , Patrick.

 

[RoryA]

Out of interest, can you give an example of a CF formula that doesn't work between languages? They should translate unless you are using something that wouldn't translate in a cell either (eg date formatting in a TEXT formula), or you're using code to assign the CF.

 

[opislak]

Out of interest, can you give an example of a CF formula that doesn't work between languages? They should translate unless you are using something that wouldn't translate in a cell either (eg date formatting in a TEXT formula), or you're using code to assign the CF.

I have to apologize for not being correct. I just tested it and now it does work fine. I remember that it didn't in the past.
At work, we have users (and Office-versions) in 3 languages (English, Dutch, French) and I remember me distributing (English) Excelfiles with a "ReadMe"-tab explaining cell colors that I used. I got responses from colleagues of another linguistic system looking for cells in certain colors. Not one cell had changed color! I then went to see these people and in the sheet, every formula was automatically translated, but not the conditional ones. Things like "Round" were not translated into the Dutch version "Afronden" or the French "Arrondi", 'Sum" should have become "Som" or "Somme", "If" into "Als" or "Si", etc. but it didn't. But just now, I tested it and now it works excellent.
So again: sorry...