 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008Custom Number Formats - Billion Dollars Rounded
Ok. Thanks to the discussion group I was given the following custom formatting setting so that I could round numbers. [>=1000000000] $#,###,, "Billion";[>=1000000]$#,, "Mil"; $#, "k" The above will formatt this $1,965,532 into this $2 Mil But will also formatt this $1,838,928 into this $2 Mil I need to adjust the custom number formatting so that I get the following instead $1.9 Mil $1.8 Mil Any suggestions?  Signature--coastal coastal, How about this? [>=1000000000] $#,###,, "Billion";[>=1000000]$#.#,, "Mil"; $#, "k" HTH, Conan > Ok. Thanks to the discussion group I was given the following custom > formatting setting so that I could round numbers. [quoted text clipped - 10 lines] > > Any suggestions? woops, I mean [>=1000000000] $#,###,, "Billion";[>=1000000]$#.0,, "Mil"; $#, "k" But 1,965,532 will round up to 2.0 mil because 60,000 is rounding up causing 900,000 to round up to the next mil 1,945,532 should round to 1.9 mil. HTH, Conan > coastal, > [quoted text clipped - 20 lines] >> >> Any suggestions? Another thing: 1,838,928,000 will be displayed as $1,839 Billion, which is technically incorrect. That would either be 1.8 billion or 1,839 million (sorry about the nerdy anal-ness). Thought I'd let you know. If it is for something official, you might want to change that or it might look like your numbers are off by 3 decimal places. If it is just for yourself, just keep that in mind. [>=1000000000] $#.0,, "Billion";[>=1000000]$#.0,, "Mil"; $#, "k" will get you "$1.8 Billion" [>=1000000000] $#,###,, "Million";[>=1000000]$#.0,, "Mil"; $#, "k" will get you "$1,839 Million" HTH, Conan > woops, > [quoted text clipped - 36 lines] >>> >>> Any suggestions? That worked great! Signature--coastal > Another thing: > [quoted text clipped - 60 lines] > >>> > >>> Any suggestions? [>=1000000000] $#,###,, "Billion";[>=1000000]$#0.0,, "Mil"; $#, "k" > Ok. Thanks to the discussion group I was given the following custom > formatting setting so that I could round numbers. [quoted text clipped - 10 lines] > > Any suggestions? Just noticed your format for billion is wrong. It's formatting 1.5 Billion as 1,500 Billion. Try this: [>=1000000000] $#,##0.0,,, "Billion";[>=1000000]$#0.0,, "Mil"; $#, "k" Tyro >[>=1000000000] $#,###,, "Billion";[>=1000000]$#0.0,, "Mil"; $#, "k" > [quoted text clipped - 12 lines] >> >> Any suggestions? Thanks! Signature--coastal > Just noticed your format for billion is wrong. It's formatting 1.5 Billion > as 1,500 Billion. Try this: [quoted text clipped - 19 lines] > >> > >> Any suggestions? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.