Number format hide errors

opislak

New Member
Joined
Feb 28, 2017
Messages
44
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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,970
To handle #DIV/0 errors, ISERROR functions would be better than formatting, to stop the error from causing further errors from subsequent calculation.
 

opislak

New Member
Joined
Feb 28, 2017
Messages
44
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

New Member
Joined
Feb 28, 2017
Messages
44

ADVERTISEMENT

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

Active Member
Joined
Nov 16, 2009
Messages
269
Hi
 

Attachments

  • Format.jpg
    Format.jpg
    81.4 KB · Views: 7

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,568
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

opislak

New Member
Joined
Feb 28, 2017
Messages
44
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Feb 28, 2017
Messages
44
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,127,612
Messages
5,625,842
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top