Format Cell

[BigBeachBananas]

Hi all,

I have the following issue with formatting numbers.

Sample positive numbers:

12.6357​

12635.7​

12,635,703.92​

12,635,703,916.48​

Current cell format: [>=1000000000]$#,##0.0,,,"B";[>=1000000]$#,##0.0,,"M";$#,##0.0,"K"
Sample positive output:

$0.0K

$12.6K

$12.6M

$12.6B

My issue is with the negative values.

$0.0K

-$12.6K

-$12,635.7K

-$12,635,703.9K

Notice the Millions and Billions are not rounded correctly as the positive version.

 

[kevin9999]

I'm not aware of any single Custom Format that will achieve your objective. There is a workaround however, using multiple custom formats couped with conditional formatting. Firstly, add the following 3 Custom Number Formats to your sheet:

$#0.0,"K";[Red]-$#0.0,"K";;@
$#0.0,,"M";[Red]-$#0.0,,"M";;@
$#0.0,,,"B";[Red]-$#0.0,,,"B";;@

Then apply Conditional Formatting selecting the appropriate number format as fits each condition:

formats.xlsx
	A	B	C	D
1	12.6357	12635.7	12,635,703.92	12,635,703,916.48
2	-12.6357	-12635.7	-12,635,703.92	-12,635,703,916.48
3
4	$0.0K	$12.6K	$12.6M	$12.6B
5	-$0.0K	-$12.6K	-$12.6M	-$12.6B
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:D5	Expression	=AND(A4<0,A4>=-1000000)	text	YES
A4:D5	Expression	=AND(A4>=0,A4<=1000000)	text	YES
A4:D5	Expression	=AND(A4<-1000000,A4>-1000000000)	text	YES
A4:D5	Expression	=AND(A4>=1000000,A4<1000000000)	text	YES
A4:D5	Expression	=A4<-1000000	text	YES
A4:D5	Expression	=A4>1000000	text	YES

 

[BigBeachBananas]

I'm not aware of any single Custom Format that will achieve your objective. There is a workaround however, using multiple custom formats couped with conditional formatting. Firstly, add the following 3 Custom Number Formats to your sheet:

$#0.0,"K";[Red]-$#0.0,"K";;@
$#0.0,,"M";[Red]-$#0.0,,"M";;@
$#0.0,,,"B";[Red]-$#0.0,,,"B";;@

Then apply Conditional Formatting selecting the appropriate number format as fits each condition:

formats.xlsx
	A	B	C	D
1	12.6357	12635.7	12,635,703.92	12,635,703,916.48
2	-12.6357	-12635.7	-12,635,703.92	-12,635,703,916.48
3
4	$0.0K	$12.6K	$12.6M	$12.6B
5	-$0.0K	-$12.6K	-$12.6M	-$12.6B
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:D5	Expression	=AND(A4<0,A4>=-1000000)	text	YES
A4:D5	Expression	=AND(A4>=0,A4<=1000000)	text	YES
A4:D5	Expression	=AND(A4<-1000000,A4>-1000000000)	text	YES
A4:D5	Expression	=AND(A4>=1000000,A4<1000000000)	text	YES
A4:D5	Expression	=A4<-1000000	text	YES
A4:D5	Expression	=A4>1000000	text	YES

This works. Appreciate the answer.

 

[kevin9999]

This works. Appreciate the answer.

Happy to help, and thanks for the feedback

 

[mj2718]

This works for a table but doesnt seem to work for data labels when you create a chart from that table.

 

[kevin9999]

This works for a table but doesnt seem to work for data labels when you create a chart from that table.

Which it was never meant to. If that is the issue you're facing, suggest you start a new thread.