Formatting $19 million
I accidentally posted this in the Access Database forum. But needed an excel
solution for the following: I have several columns with currency amounts from in the hundred thousands to the billions. It hard to read these colums so I want to format each to the rounded million, or billion in a separate column. So what I have is $190,111,111.11 $2,111,111,111.11 I want a separate column with the following (still keeping the other column): $190 million $2 billion Is there a formatting tool, or formula to get those results? -- --coastal -- --coastal |
Formatting $19 million
=round(number/1000000,0) will give you the rounded up number in millions.
You can format the second column to show it "$19 million". You can also use ROUNDUP and ROUNDDOWN if you want to move the number in a particular direction. The HELP function in Excel on these functions explains it quite well. Prashant Rao "coastal" wrote: I accidentally posted this in the Access Database forum. But needed an excel solution for the following: I have several columns with currency amounts from in the hundred thousands to the billions. It hard to read these colums so I want to format each to the rounded million, or billion in a separate column. So what I have is $190,111,111.11 $2,111,111,111.11 I want a separate column with the following (still keeping the other column): $190 million $2 billion Is there a formatting tool, or formula to get those results? -- --coastal -- --coastal |
Formatting $19 million
You can do it with a custom format. Select the cells you want and
Choose Format|Cells - Number - Custom and use this [=1000000000] #,,, "Billion";#,, "Million" -- HTH... Jim Thomlinson "coastal" wrote: I accidentally posted this in the Access Database forum. But needed an excel solution for the following: I have several columns with currency amounts from in the hundred thousands to the billions. It hard to read these colums so I want to format each to the rounded million, or billion in a separate column. So what I have is $190,111,111.11 $2,111,111,111.11 I want a separate column with the following (still keeping the other column): $190 million $2 billion Is there a formatting tool, or formula to get those results? -- --coastal -- --coastal |
Formatting $19 million
Thanks Jim, this helped. But now I need to adjust it to include data in the
thousands. $19,000. I tried adding the following to the tail end of the custom formatting but it did not work: ;#,,, "Thousand" Have any suggestions? -- --coastal "Jim Thomlinson" wrote: You can do it with a custom format. Select the cells you want and Choose Format|Cells - Number - Custom and use this [=1000000000] #,,, "Billion";#,, "Million" -- HTH... Jim Thomlinson "coastal" wrote: I accidentally posted this in the Access Database forum. But needed an excel solution for the following: I have several columns with currency amounts from in the hundred thousands to the billions. It hard to read these colums so I want to format each to the rounded million, or billion in a separate column. So what I have is $190,111,111.11 $2,111,111,111.11 I want a separate column with the following (still keeping the other column): $190 million $2 billion Is there a formatting tool, or formula to get those results? -- --coastal -- --coastal |
Formatting $19 million
[=1000000000] #,,, "Billion";[=1000000]#,, "Million"; #, "Thousand"
-- HTH... Jim Thomlinson "coastal" wrote: Thanks Jim, this helped. But now I need to adjust it to include data in the thousands. $19,000. I tried adding the following to the tail end of the custom formatting but it did not work: ;#,,, "Thousand" Have any suggestions? -- --coastal "Jim Thomlinson" wrote: You can do it with a custom format. Select the cells you want and Choose Format|Cells - Number - Custom and use this [=1000000000] #,,, "Billion";#,, "Million" -- HTH... Jim Thomlinson "coastal" wrote: I accidentally posted this in the Access Database forum. But needed an excel solution for the following: I have several columns with currency amounts from in the hundred thousands to the billions. It hard to read these colums so I want to format each to the rounded million, or billion in a separate column. So what I have is $190,111,111.11 $2,111,111,111.11 I want a separate column with the following (still keeping the other column): $190 million $2 billion Is there a formatting tool, or formula to get those results? -- --coastal -- --coastal |
All times are GMT +1. The time now is 01:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com