Formatting zeros as dashes

A

Arun

I have a worksheet with the data formatted as currency with no decimal
places. I would like all my zeros to show up as dashes. For example, no $
0 or $ (0). I understand that if the number is not exactly zero, it won't
use the dash, ie. a .25 will show up as a 0... but this is what I would like
to override. I also don't want to actually change the value in the cell
using the rounding functions.

Simply put, how do I get it to choose the format based upon the display
accuracy of the value versus the true value?
 
B

Bernard Liengme

Ann:
If you think about it there is no way for Excel to do this. How close to
zero must the value be to be shown as a dash? Excel cannot make that guess.

You could use a helper column with ROUND and hide the original column
best wishes
 
×

מיכ×ל (מיקי) ×בידן

If I understtod you correct - it is very simple to accomplish your request.
Use "custom Format" - such as:
[<1]"-";[>=1]#,##0.00;General
[<0.05]"-";[>=0.05]#,##0.00;General
 
×

מיכ×ל (מיקי) ×בידן

If the presence of the $ sign is essential - you might consider something
like:
[<1]"-";[>=1]$ #,##0;General
Micky


מיכ×ל (מיקי) ×בידן said:
If I understtod you correct - it is very simple to accomplish your request.
Use "custom Format" - such as:
[<1]"-";[>=1]#,##0.00;General
[<0.05]"-";[>=0.05]#,##0.00;General
---------------------------------------
The first will display a dash for every value smaller than 1
The second will display a dash for every value smaller than 0.05
So, what you have to decide is the exact threshold of your needs.
Micky


Arun said:
I have a worksheet with the data formatted as currency with no decimal
places. I would like all my zeros to show up as dashes. For example, no $
0 or $ (0). I understand that if the number is not exactly zero, it won't
use the dash, ie. a .25 will show up as a 0... but this is what I would like
to override. I also don't want to actually change the value in the cell
using the rounding functions.

Simply put, how do I get it to choose the format based upon the display
accuracy of the value versus the true value?
 
×

מיכ×ל (מיקי) ×בידן

Not to mention:
[<1]"-";[>=1]$ #,##0.00;General
in order to display 2 decimal digits.
Micky


מיכ×ל (מיקי) ×בידן said:
If the presence of the $ sign is essential - you might consider something
like:
[<1]"-";[>=1]$ #,##0;General
Micky


מיכ×ל (מיקי) ×בידן said:
If I understtod you correct - it is very simple to accomplish your request.
Use "custom Format" - such as:
[<1]"-";[>=1]#,##0.00;General
[<0.05]"-";[>=0.05]#,##0.00;General
---------------------------------------
The first will display a dash for every value smaller than 1
The second will display a dash for every value smaller than 0.05
So, what you have to decide is the exact threshold of your needs.
Micky


Arun said:
I have a worksheet with the data formatted as currency with no decimal
places. I would like all my zeros to show up as dashes. For example, no $
0 or $ (0). I understand that if the number is not exactly zero, it won't
use the dash, ie. a .25 will show up as a 0... but this is what I would like
to override. I also don't want to actually change the value in the cell
using the rounding functions.

Simply put, how do I get it to choose the format based upon the display
accuracy of the value versus the true value?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top