Forum Home
Press F1
Thread ID: 100897	2009-06-24 13:23:00	Excel format issue	mattypoll (15037)	Press F1

Post ID	Timestamp	Content	User
785560	2009-06-24 13:23:00	I am very confused and need some help . I am tryint to format a cell in Excel to give a - instead of a zero so created as following custom number #,##0;(#,##0);"-" This works when the entry is zero, but not when, say it is 0 . 1 and rounds down to zero . In this instance it returns 0 . I have search the forum and not found an answer . Anyone got any clues? I know there are set examples where it does this, but I would like to understand how it works . Thank you for your help, Matt	mattypoll (15037)
785561	2009-06-25 00:20:00	Is there a reason why using the accounting format is no good? Edit: Yep. A very good reason.	the_bogan (9949)
785562	2009-06-25 09:04:00	Hi Matt and welcome to Press F1! The reason it isn't showing the minus symbol is because the value isn't zero. The third condition only applies to zero values. BTW you don't need to enclose the 3rd condition in quotes. I believe you have a couple of choices here. One is to force the value to 0 using a round function anywhere you have a value being picked up from somewhere else or derived from other figures (like sum totals). If this is possible then the formula should be enclosed in: =ROUND( [your formula], 0). Then your custom format will work as intended. Another option is to use a condition within your custom number format like this: [>=1]#,##0;[<=-1](#,##0);- This only applies the #,##0 format to positive values greater than or equal to 1. It applies the (#,##0) format to numbers less than or equal to -1. The - symbol is used for all other values. However, the issue with this approach is it shows a double minus symbol for values between (but not including) 0 and -1. The first minus symbol denotes a negative number and the second minus symbol denotes the zero. So effectively it is showing -0, but is displayed as --. This was tested using Excel 2003. HTH, Andrew	andrew93 (249)
1