order of magnitude constants

N

Need Help!

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.
 
C

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)
 
J

JE McGimpsey

One way, assuming only positive numbers:

Format/Cells/Number/Custom

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

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
 
Top