remove negative sign on cell format

[Faridwahidi]

Hi,

I have a cell format to display negative large numbers in bracket. However, when the value is below 1 million, there is negative sign in front of bracket.


Format Cell : [<-999999999](#,###.00,,, "B");[<-999999](#.00,, "M");(#.00, "K")

(5.00M) - OK
-(500.00K) >> to remove negative sign


can anyone help me?

[FDibbins]

How is this different from your thread here...
https://www.excelforum.com/excel-for...ive-value.html

[Faridwahidi]

Hi FDbbins,

The difference is the old thread does not display negative sign in bracket.

The old thread has met the objective to display value in negative sign. That's why I have marked solved.

can you help for this thread, Sir?

[Bo_Ry]

Add another Conditional formatting

=(A1<0)*(A1>-999999)

with custom number format

0;(#.00, K);0

[Faridwahidi]

Hi Bo_RY,


Thanks. Finally it works with 3 conditions

=A1>0 [>999999999] #,###.00,,, "B";[>999999]#.00,, "M";#.00, "K"
=A1<0 [<-999999999] (#,###.00,,, "B");[<-999999](#.00,, "M");(#.00, "K")
=(A1<0)*(A1>-999999) 0;(#.00, K);0


1 more issue, besides the above method I have use this format, [<=-1000000](#.00,,"M");[<=-1000](#.00,"K")
It works for negative Million and Thousands only.

However, When I modify and add Billions, excel not accepted the format
[<=-1000000000](#,###.00,,,"B");[<=-1000000](#.00,,"M");[<=-1000](#.00,"K")

Can you tell me what went wrong with the formula?

[RaulSerg]

You can only use two conditions this way.

For more sophisticated solutions, you need to turn to conditional formatting.

[Faridwahidi]

Hi Raul,

If the below format cannot be used, Microsoft should look into this matter and solve it for the next version release.
[<=-1000000000](#,###.00,,,"B");[<=-1000000](#.00,,"M");[<=-1000](#.00,"K")



Then, from where negative sign coming when the value in thousands also make me totally confused
[<-999999999] (#,###.00,,, "B");[<-999999](#.00,, "M");(#.00, "K")


anyway, thanks for clarification.

[RaulSerg]

Hi, I don't Microsoft will change this. Why should they?

Complex formatting scenarios are simply not meant to be solved via custom number formats but specifically through conditional formatting.
Conditional formatting has been improved significantly over the last 10-15 years (and custom number formats have not), so there's already a tool available to do what - I think - you're trying to achieve. (At least that would be Microsoft's natural response I assume.)
The problem to me seems to that you wish to squeeze a lot functionality into a tool which is meant to provide only basic solutions.

e.g.: custom number formats also provide the ability to change the font color but only with a very limited color palette, not with full RGB codes; for that you would need to turn to conditional formatting, too.

Re your last question for: [<-999999999] (#,###.00,,, "B");[<-999999](#.00,, "M");(#.00, "K")
The third condition is the one that applies to all other values, so it retains the sign depending on how you structure your thousands delimiters with the commas. It just reinforces the idea that custom number formats are not meant for such situations:
with a format of (#.00, "K"):
-1 becomes (0.00K) because it gets rounded/cut-off basically at zero, therefore it shows no sign (However, a format of e.g. (#.000,"K") would show -(.001K) - which I believe is consistent and logical)
-10 becomes -(.01K) which is rounded/cut-off correctly, so retains it sign

Hope this helps.

Regards

[Faridwahidi]

Hi Raul,

Thanks for very detailed explanation.