Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Zero values displaying as "-"
Hi everyone. I have created a new custom number format that replaces 0
values with a dash (-). But there's a problem with this. In cases where the zero value is generated from a formula that takes the value in one cell and subtracts the value in another; and the result is 0, the 0 is in brackets (0). I've tried everything under the sun, but no can do! Any clues anyone? TIA! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Zero values displaying as "-"
Are you sure it's zero, copy it to another call and paste special as values
and see what you get, byw how does your custom format look like copied from the format windowscustom? -- Regards, Peo Sjoblom "Uncle Ben" wrote in message ... Hi everyone. I have created a new custom number format that replaces 0 values with a dash (-). But there's a problem with this. In cases where the zero value is generated from a formula that takes the value in one cell and subtracts the value in another; and the result is 0, the 0 is in brackets (0). I've tried everything under the sun, but no can do! Any clues anyone? TIA! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Zero values displaying as "-"
Maybe your zero is not zero but some small number greater than 0. Increase
the decimal places on it and check. -- HTH RP (remove nothere from the email address if mailing direct) "Uncle Ben" wrote in message ... Hi everyone. I have created a new custom number format that replaces 0 values with a dash (-). But there's a problem with this. In cases where the zero value is generated from a formula that takes the value in one cell and subtracts the value in another; and the result is 0, the 0 is in brackets (0). I've tried everything under the sun, but no can do! Any clues anyone? TIA! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Zero values displaying as "-"
"Peo Sjoblom" wrote in message
... Are you sure it's zero, copy it to another call and paste special as values and see what you get, byw how does your custom format look like copied from the format windowscustom? -- Regards, Peo Sjoblom You're absolutely right, it's not zero, I have cents in there. The cells are formatted to 0 decimals - so even though the sum is 0, Excel gives me (0) because of the hidden decimals. How do I get cells where the decimal value is -0.50 to display as "-"? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Zero values displaying as "-"
Here is the formatting I'm currently using:
_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) This allows me to display all zero amount cells as "-" instead of 0's. The problem I have is for cells that contains small negative balances under ..50 - those cells do not format to "-" but to (0). So the report looks silly, with -'s on all zero+'s and (0) on all zero-'s. Is there a cure for this grand malaise? TIA. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Zero values displaying as "-"
Round it to 0 decimal places.
-- HTH RP (remove nothere from the email address if mailing direct) "Uncle Ben" wrote in message ... Here is the formatting I'm currently using: _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) This allows me to display all zero amount cells as "-" instead of 0's. The problem I have is for cells that contains small negative balances under .50 - those cells do not format to "-" but to (0). So the report looks silly, with -'s on all zero+'s and (0) on all zero-'s. Is there a cure for this grand malaise? TIA. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Zero values displaying as "-"
"Bob Phillips" wrote in message
... Round it to 0 decimal places. -- No, that's won't work, Bob, because the underlying numbers do carry two decimal places. If I round to 0 decimal places, I'll end up with cents elimination differences throughout the report. So because the input to the spreadsheet is to two decimal places, I don't think I have much choice but to round to 2 decimal places, but format to 0 decimal places (because for reporting purposes, I don't want to see decimals.) Everything works 100%, except for those -Zeros... I guess one quick way is just to go in and override... but there has to be an easier way. I've been playing with the IF function, that I could incorporate in the formula ... but I'm not quite there yet. But thanks ... any other ideas? |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Zero values displaying as "-"
There is an easier way but make sure you remember to turn this off when you
are not using this sheet, if you format for zero decimals you can go to toolsoptionscalculation and check precision as displayed -- Regards, Peo Sjoblom "Uncle Ben" wrote in message ... "Bob Phillips" wrote in message ... Round it to 0 decimal places. -- No, that's won't work, Bob, because the underlying numbers do carry two decimal places. If I round to 0 decimal places, I'll end up with cents elimination differences throughout the report. So because the input to the spreadsheet is to two decimal places, I don't think I have much choice but to round to 2 decimal places, but format to 0 decimal places (because for reporting purposes, I don't want to see decimals.) Everything works 100%, except for those -Zeros... I guess one quick way is just to go in and override... but there has to be an easier way. I've been playing with the IF function, that I could incorporate in the formula ... but I'm not quite there yet. But thanks ... any other ideas? |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Zero values displaying as "-"
"Bob Phillips" wrote in message
... Round it to 0 decimal places. -- HTH RP (remove nothere from the email address if mailing direct) Well Bob, I retract what I said earlier. Even though I was correct in my response about rounding, your suggestion worked on that one column of the spreadsheet where I was having this (0)'s showing. Even though the entire spreadsheet is rounded to 2 decimal places, with 0 decimal on formatting, that last column shows properly if I round it to 0 decimal places ... And that should be fine, because all the numbers making up that total column have been rounded to 2 decimal places. Thanks for your help and everyone else who have come to the rescue. Much appreciated. Regards, Uncle Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions | |||
Displaying null values in charts | Charts and Charting in Excel | |||
#N/A Values : Returned by Formulas vs Entered Manually | Charts and Charting in Excel | |||
Displaying values from zero on graph | Excel Discussion (Misc queries) |