Customer format millions and thousands

[Access Beginner]

Hi All,


I have cells, where the value can can vary from the millions to the tens of thousands.

Do you know of any custome format options, if the value is say 5,100,000 we can show this as 5.1M and if the value is say 320,500 we can show it as 320.5T.

Values are in a dynamic chart, so a user can select a region and the values update, for that particular region. The issue is where some regions can go into the millions, where others will only go into the hundreds of thousand and possibly tens of thousands

These values go into a dynamic chart and showing the value as 5,000,000 is taking up too much real estate. If all the values were in the million, this would not be a problem and I could use s aimpel customer format, however when the values vary as mentioned above, I'm stumped.

Any help/advice appreciated.

Cheers

 

[grd]

You could use Conditional Formatting with the Custom formats of [#,##0 ] for numbers less than 1,000 (note spaces after the last zero to maintain alignment. [#, "k"] for Thousands and [#,, "m"] for Millions excluding the square brackets.

HOLD on that - need to check something

 

[hippiehacker]

this should display 5.100.000 as 5.1M
#,#.."M"

this should display 320.500 as 320,5K
#.##0,0."K"

 

[hippiehacker]

this should display 5.100.000 as 5.1M
#,#.."M"

this should display 320.500 as 320,5K
#.##0,0."K"

 

[grd]

Try again as my last formats did not allow for decimal places eg 5.1 m

Use Conditional Formatting with the Custom formats of [#,##0 ] for numbers less than 1,000 (note you can use spaces after the last zero to maintain alignment if deemed necessary. [#.00, "k"] for Thousands and [#.00,, "m"] for Millions excluding the square brackets.

These options round the reduced number to 2 decimal places. You could use #.0,, "m" if only 1 decimal place required or #.###,, "m" to allow flexible decimals.

 

[Rick Rothstein]

Give this Custom Cell Format a try...

[<1000000]0.0,"T";0.0,,"M"

 

[grd]

Give this Custom Cell Format a try...

[<1000000]0.0,"T";0.0,,"M"

I like that better - didn't know you could include conditions in formats

Could add a further one for <1,000

[<1000]#;[<1000000]#.##,"T";#.##,,"M"

 

[Access Beginner]

Give this Custom Cell Format a try...

[<1000000]0.0,"T";0.0,,"M"

Thank Rick worked great, just what I was after.

Thanks to grd and hippiehacker for responding also.

Cheers

 

[ErinB007]

Is there a way to do the opposite? For instance, if I have data as 1.5K, but I want it to read 1,500.

 

[jtakw]

Hi,

Unfortunately, I don't think custom cell formatting can convert text to numbers, so, for your example of 1.5K, you can use:

Excel 2010
	A	B
1	1.5K	1,500.00
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,LEN(A1)-1)*1000