Number Formatting Problem

[seguin85]

Hi,

I have written the following number formatting code for a graph:
[>1000000]$0.00,,"M";[>100000]$0,"K";$0.0,"K"

Where if it is over 1 million it will give two decimal place, if it if over 100,000 it will give 0 decimal places, otherwise it will give 1 decimal place.

I want this because I always want the data labels to be 3 decimal places.

Problem is if a value is 0 it shows up as $0.0K where I would like it to be blank.

Any ideas?

 

[JoeMo]

Hi,

I have written the following number formatting code for a graph:
[>1000000]$0.00,,"M";[>100000]$0,"K";$0.0,"K"

Where if it is over 1 million it will give two decimal place, if it if over 100,000 it will give 0 decimal places, otherwise it will give 1 decimal place.

I want this because I always want the data labels to be 3 decimal places.

Problem is if a value is 0 it shows up as $0.0K where I would like it to be blank.

Any ideas?

Try:

[>1000000]$0.00,,"M";[>100000]$0,"K";

 

[seguin85]

If the value is greater than 0 but less than 100,000 it also shows up as blank.

I'd like it to show up as $##.#K

 

[Rick Rothstein]

If the value is greater than 0 but less than 100,000 it also shows up as blank.

I'd like it to show up as $##.#K

You can apply a Conditional Format over the cells to do that. Select your cells and note what the active cell is, let's say for example purposes that the active cell is A1. Use this Conditional Formatting formula...

=A1=0

and set the format for it as the Custom Format "" (just two quote marks, nothing else). Now, if the value is not 0, your cell's Custom Format will apply, but if the cell value is 0, then the Conditional Formatting's Custom Format will override your cell's Custom Format and display nothing.

 

[seguin85]

Thanks Rick!

That worked perfect!