Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Replacing zero values with dashes
I have a formula in a cell which is calculating from several cells. When the value comes out at zero, I want a dash to display, not zeros. I have read the tip on this subject, and tried it, but it does not work! :( I have used the following syntax: #,##0.00;[Red](#,##0.00);- ; This should do the following: 1. When the result is positive, the display should have two decimal places with a thousands separator. 2. When the results is negative, the display should have all the above, but be red and have brackets around it. 3. When the result is zero, only a dash should be visible. When my formula has a zero result, it shows "0.00" or "(0.00)"! Can anybody tell me why?? Jonathan -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=465821 |
#2
|
|||
|
|||
Jonibenj Wrote: I have a formula in a cell which is calculating from several cells. When the value comes out at zero, I want a dash to display, not zeros. I have read the tip on this subject, and tried it, but it does not work! :( I have used the following syntax: #,##0.00;[Red](#,##0.00);- ; This should do the following: 1. When the result is positive, the display should have two decimal places with a thousands separator. 2. When the results is negative, the display should have all the above, but be red and have brackets around it. 3. When the result is zero, only a dash should be visible. When my formula has a zero result, it shows "0.00" or "(0.00)"! Can anybody tell me why?? Jonathan Hi Jonathan Try this _-*#,##0.00_-;[RED]-*#,##0.00_-;_-*"-"??_-;_-@_- -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=465821 |
#3
|
|||
|
|||
Hi
Rather than changing the display format you can change the o/p itself... this can be done by using following formula =if( yourformula=0,"-",yourformula) this means when the answer is 0 it will return dash otherwise the result. added to this you can also keep the formatting #,##0.00;[Red](#,##0.00) so that negative values would be shown as red Regards NC |
#4
|
|||
|
|||
Picky point:- XL Help suggests
#,##0.00_);[Red](#,##0.00) so that the positive numbers line up with the negative numbers. Incidentally a custom format of #,###.00;[Red] (#,###.00);-;@ works for me in XL97 -- Regards Sandy Replace@mailinator with @tiscali.co.uk "NC" wrote in message oups.com... Hi Rather than changing the display format you can change the o/p itself... this can be done by using following formula =if( yourformula=0,"-",yourformula) this means when the answer is 0 it will return dash otherwise the result. added to this you can also keep the formatting #,##0.00;[Red](#,##0.00) so that negative values would be shown as red Regards NC |
#5
|
|||
|
|||
Hi Paul, Sorry, but Excel will not accept this format - it tells me to use one of the custom formats! Jonathan -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=465821 |
#6
|
|||
|
|||
Dear NC, This looked like a good option, however, it didn't work! I still got a result of 0.00! Unbelievable, eh?!! Any more ideas? Jonathan -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048 View this thread: http://www.excelforum.com/showthread...hreadid=465821 |
#7
|
|||
|
|||
Jonibenj Wrote: Hi Paul, Sorry, but Excel will not accept this format - it tells me to use one of the custom formats! Jonathan Hi Jonathan I'm using excel 2000 and that is one of the custom functions, scroll down the list it's either third from bottom or bottom of the list, can't remember which -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=465821 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Min values in a list of numbers | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Filtering and replacing values through a macro | Excel Worksheet Functions | |||
How do I stop excel replacing numerical values with the date? | Excel Discussion (Misc queries) |