Format Cells > Custom to show numbers in millions, thousand and hundreds

[Rainbow_P]

Good day!


[>999999]$ #.0,,"M";$#," K"

Format Cells > Number > Custom

Above is the line that I am using for custom format. For example, with the cell value 1234567, when the format applied, it will show 1.2M. (cell value 1234, it shows $1K)

If the cell value is 123, it shows "$ K".

Is there a way to let it show in hundred too? For example when the cell value is 123, it shows "1 Hrd."

Thank you.


p.s. I've tried [>999999]$ #.0,,"M";$#," K";$0.0," Hrd" but it seems the condition only limited to millions and thousands only.

 

[OaklandJim]

If I understand the need then use nested if statements. If the value is in B10 then this formula should work.

=IF(B10>=10^6,B10/10^6&" M", IF(B10>=10^3,B10/10^3& " K", IF(B10>=100, B10/100 & " Hrd", "") ) )

FormatValuesOrdersOfMagnitude.xlsm
	B	C
10	1200000	1.2 M
11	1200	1.2 K
12	120	1.2 Hrd
Sheet1

Cell Formulas
Range	Formula
C10:C12	C10	=IF(B10>=10^6,B10/10^6&" M", IF(B10>=10^3,B10/10^3& " K", IF(B10>=100, B10/100 & " Hrd", "") ))

 

[Rainbow_P]

If I understand the need then use nested if statements. If the value is in B10 then this formula should work.

=IF(B10>=10^6,B10/10^6&" M", IF(B10>=10^3,B10/10^3& " K", IF(B10>=100, B10/100 & " Hrd", "") ) )

FormatValuesOrdersOfMagnitude.xlsm
	B	C
10	1200000	1.2 M
11	1200	1.2 K
12	120	1.2 Hrd
Sheet1

Cell Formulas
Range	Formula
C10:C12	C10	=IF(B10>=10^6,B10/10^6&" M", IF(B10>=10^3,B10/10^3& " K", IF(B10>=100, B10/100 & " Hrd", "") ))

@OaklandJim, thank you for your reply!

However I wish for a solution in cell's custom format, not a formula.

 

[OaklandJim]

I'm pretty sure that you can not do what you want with number formatting. BUT, I am not an expert. Doesn't the formula provided give the values that you want in the format that you want?

 

[Fluff]

I'm with OaklandJim, you cannot use that type of format for 100s.

 

[Rainbow_P]

I'm pretty sure that you can not do what you want with number formatting. BUT, I am not an expert. Doesn't the formula provided give the values that you want in the format that you want?

@OaklandJim, thanks again for the reply - I was just looking for a solution other than formula.

Wish all a nice weekend!