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]
