In Format | Cells | Numbers select "User defined" in the Category and
then in the Format Code type #K or #M or #B
Obviously you can show varying numbers of decimal places by replacing #
with #.# (1 dec place) or #.## (2 dec places) etc.
Neil
Don Daugherty wrote:
WANG, Xiaoyun wrote:
Hi, gurus,
Is there an easy way to convert a number in a cell to a rounded value
with
suffix of 'K', 'M', 'B'?
For example: 1,000 -> 1.0K; 1,111 -> 1.1K; 1,234,567 -> 1.2M
Thanks in advance.
Unless there is a format code somewhere that does it (I haven't found
one,) the only thing I know of that would give this 'appearance' would
be to insert an extra column next to the column of cells containing
the numbers you want to 'format' and make it show the text equivalent
of what you want. For example if column C contains the actual data,
Insert a blank column to the right of C, (pushing the original D and
above one letter higher). Then enter (using row 10 as an example)
into cell D10 the following formula:
=IF(C10<1000;C10;IF(AND(C10>=1000;C10<1000000);TEXT(C10/1000;"###.#")&"
K";TEXT(C10/1000000;"###.#")&" M"))
Recognize that D10 does not contain the actual value of C10, so you
would still need to refer to C10 for any subsequent calculations. You
could, however, hide column C if you wanted to.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
