order of magnitude constants

[Guest]

I was wondering if it is possible to get excel to automatically convert
numbers from lets say 10 000 to 10k, or 10 000 000 to 10M etc.

 

[Chip Pearson]

There is only limited support for formatting:

[<1000000]0,K;[<1000000000]0,,\M

This changes the displayed text but does not change the actual value of the
cell, so math will still work. If all you want it the text in the cell, use
a formula like the following:

=CHOOSE(TRUNC(LOG10(A1)),A1,A1,A1/1000&"K",A1/1000&"K",A1/1000&"K",A1/1000000&"M")

This handles numbers up to 7 digits. Add more options to the CHOOSE function
for more numbers.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[JE McGimpsey]

One way, assuming only positive numbers:

Format/Cells/Number/Custom

[<1000]General;[<999500]##0,K;#,##0,,\M

 

[JE McGimpsey]

One potential niggle. Using

[<1000000]0,K

will result in values between 999500 and 999999.999999999 being
displayed as

1000K

that may be fine, but it may give a more consistent result to use

[<999500]0,K;0,,\M

which will display those values as

1M