Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
order of magnitude constants
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
order of magnitude constants
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) "Need Help!" <Need wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
order of magnitude constants
One way, assuming only positive numbers:
Format/Cells/Number/Custom [<1000]General;[<999500]##0,K;#,##0,,\M In article , Need Help! <Need wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
order of magnitude constants
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 In article , "Chip Pearson" wrote: There is only limited support for formatting: [<1000000]0,K;[<1000000000]0,,\M |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Constants In Formulae | Excel Discussion (Misc queries) | |||
Flagging constants | Excel Worksheet Functions | |||
What is a relationship magnitude graphic chart | Charts and Charting in Excel | |||
More than 2 constants in Index | Excel Worksheet Functions | |||
constants | Excel Discussion (Misc queries) |