On Thu, May 15, 2008 at 6:36 AM, Don Daugherty <[EMAIL PROTECTED]> 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] > > Hi, Don, Thanks for the reply. It's also the only way I can think of at the moment. -- WANG, Xiaoyun Shanghai, China
