Custom Formatting

[ypurcaro]

Hello All,

I have a custom format for the number as below
100 - 0.1K
1000 - 1.0K
10000 - 10.0K
200000 - 200.0K
4000000 - 4Mil

Here is what I entered to the custom format:
[>=1000000]#,##0,,"Mil";[<1000000]#,##0.0,"K";General

Now, for the 100, I don't want it to read "0.1K", how should I fix that?

Thank you!

 

[jasonb75]

Try this way instead,

[>=1000000]#,##0,,"Mil";[<1000]General;#,##0.0,"K";@

With custom parameters the first format is for values greater than specified, the second is for values less then specified and the third is for anything between the two specified values.
The final @ is for anything that is not numeric.

 

[ypurcaro]

Try this way instead,

[>=1000000]#,##0,,"Mil";[<1000]General;#,##0.0,"K";@

With custom parameters the first format is for values greater than specified, the second is for values less then specified and the third is for anything between the two specified values.
The final @ is for anything that is not numeric.

Hi jasonb75,

Thank you for your response. The code works fine if the numbers are positive; however, when the numbers are negative, it didn't work. I had a negative number like this (8,426,009). After I applied the custom format, it became -8426008.99 It seems like it's totally off.

Thanks again!

 

[jasonb75]

That might need a bit of creativity with conditional formatting.

Should negatives have the same format as positives, e.g. something like (8.4m)

 

[ypurcaro]

That might need a bit of creativity with conditional formatting.

Should negatives have the same format as positives, e.g. something like (8.4m)

Yes, same format.
Thank you.

 

[jasonb75]

Try with the cell format from my earlier post and this one applied by conditional formatting to cells with a value <0

[Red][<=-1000000](#,##0,,"Mil");[Red][>-1000](#0);[Red](#,##0.0,"K");@

Not sure why some have the leading - sign and others don't, I'll look into that in more detail later if necessary.

Book1 (version 1).xlsb
	A	B
1	100	-(100)
2	1.0K	-(1.0K)
3	10.0K	-(10.0K)
4	100.0K	-(100.0K)
5	1Mil	(1Mil)
Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:B5	Cell Value	<0	text	NO

 

[ypurcaro]

Try with the cell format from my earlier post and this one applied by conditional formatting to cells with a value <0

[Red][<=-1000000](#,##0,,"Mil");[Red][>-1000](#0);[Red](#,##0.0,"K");@

Not sure why some have the leading - sign and others don't, I'll look into that in more detail later if necessary.

Book1 (version 1).xlsb
	A	B
1	100	-(100)
2	1.0K	-(1.0K)
3	10.0K	-(10.0K)
4	100.0K	-(100.0K)
5	1Mil	(1Mil)
Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:B5	Cell Value	<0	text	NO

Hello there,

I tried to play around the formula but have no luck with it. Anyway, thank you for helping me.