Formatting zeros as dashes

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

 

[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

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

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

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

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

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?