Custom format

[madocar]

Hi everyone,

how can I modify my custom number format to show negative number as well. Is it even possible to show these custom format numbers below zero

[>=1000000]#,#0 " M";[>=1000]0 " K";0

Thanks

 

[bobhc]

Good day madocar

Try here

http://office.microsoft.com/en-001/excel-help/create-a-custom-number-format-HP010342372.aspx

 

[madocar]

Sorry
Did not help to solve the issue

 

[madocar]

Here's how it looks

 

[NARAYANK991]

Hi ,

Do you think this will be of help ?

http://excelribbon.tips.net/T010227_Handling_Negative_Numbers_in_a_Complex_Custom_Format.html

Narayan

 

[Xiq]

What about Conditional Formating?

 

[Xiq]

See attached file for an example with Conditional Formating

 

[SirJB7]

Hi, madocar!

New avatar I guess... hope it's a picture of you... or at least you got the phone number

Formatting conditions are originally defined for 4 case:
Positive; Negative; Zero; Text
However the conditions proved to be somehow flexible but somehow rigid as well. Flexible since you could use them for number ranges instead of the standard categories but rigid since you can only have 4.

About your question I'm not sure if you want to get the numbers displayed as in Xiq's file:
111.207.628 M
115.259 K
628
-653
-9.280 K
-115.259.280 M
...or as:
111 M
115 K
628
-653
-9 K
-115 M

However you want, you can't do it directly from cell custom format, as you require 6 or 7 conditions.

Now, if you're after the 1st examples go with Xiq's solution, but if you're after then 2nd ones, you'll have to use a helper column as follows:
=A1/10^(ENTERO(LOG(ABS(A1))/3)*3) -----> in english: =A1/10^(INT(LOG(ABS(A1))/3)*3)
and apply a CF to it depending on original A1 values (as in Xiq uploaded file).

Maybe this helps too.
http://chandoo.org/forum/threads/cell-format-negative-numbers.12766/#post-75103

Regards!

 

[madocar]

Hi guys,

thank u so much for your advices.
I finally managed it .... almost. I use this format

[>=1000000]#,#0 " M";[>=1000]0 " K";0 " K"

It shows numbers almost exactly as I wanted. Except "Zero" value shows like 0 K, which doesn't make any sence.
It would be better to show Zero value like "0" with no letter. This I could not manage. Excel file in attachment

 

[SirJB7]

Hi, madocar!

Have you checked the uploaded file? I don't happen to see any cell formatted as described.

Despite of this try, you're only using 3 conditions (of the 4 available), and according to them your negative values should be displayed unsigned and with the " K" suffix independently of their value, am I wrong?
1st condition: >= 1000000
2nd condition: >= 1000
3rd condition: implicitly for <1000 (which include zero and all negative numbers)

Regards!

 

[madocar]

Hi SirJB7,

Basically I don't need to change any cell format.
I modified custom formating in graph - vertical axis

 

[SirJB7]

Hi, Madocar!
Then glad you solved it, I should have misread you. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!