I'm using a custom number format and conditional formatting in cells so that the displayed value is different than the actual value. The custom number format is [<=500]$#,",000" and changes a number like $125,600 to $126,000 (i.e. it rounds it to the nearest thousand). However, with negative numbers, it leaves the "-" in front of the number. I'd like to address it so that it changes it to parenthesis instead of the negative sign, but with the customer format I'm using, I haven't had success incorporating it. I tried different versions on my own and none of them help.
1 Answer
This should be what you're after
[>=500]$#,",000";[<=-500]($#,",000") The formatting after the semi-colon applies to negative numbers, by using a different condition <=-500. The first condition, >=500, will only be appropriate for positive numbers.
If you want to wrap your negative numbers in square brackets rather than parentheses, you need to escape the brackets like this
[>=500]$#,",000";[<=-500]\[$#,",000"\] 
"in that format somewhere? That doesn't work for me.