Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 7th 08, 10:51 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 25
Default Custom 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?

--
--coastal

  #2   Report Post  
Old February 7th 08, 10:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 419
Default Custom Number Formats - Billion Dollars Rounded

coastal,

How about this?

[=1000000000] $#,###,, "Billion";[=1000000]$#.#,, "Mil"; $#, "k"

HTH,

Conan




"coastal" wrote in message
...
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?

--
--coastal



  #3   Report Post  
Old February 7th 08, 10:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 1,091
Default Custom Number Formats - Billion Dollars Rounded

[=1000000000] $#,###,, "Billion";[=1000000]$#0.0,, "Mil"; $#, "k"

"coastal" wrote in message
...
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?

--
--coastal



  #4   Report Post  
Old February 7th 08, 11:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 419
Default Custom Number Formats - Billion Dollars Rounded

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





"Conan Kelly" wrote in message
...
coastal,

How about this?

[=1000000000] $#,###,, "Billion";[=1000000]$#.#,, "Mil"; $#, "k"

HTH,

Conan




"coastal" wrote in message
...
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?

--
--coastal





  #5   Report Post  
Old February 7th 08, 11:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2007
Posts: 1,091
Default Custom Number Formats - Billion Dollars Rounded

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

"Tyro" wrote in message
...
[=1000000000] $#,###,, "Billion";[=1000000]$#0.0,, "Mil"; $#, "k"

"coastal" wrote in message
...
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?

--
--coastal







  #6   Report Post  
Old February 7th 08, 11:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 419
Default Custom Number Formats - Billion Dollars Rounded

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



"Conan Kelly" wrote in message
...
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





"Conan Kelly" wrote in message
...
coastal,

How about this?

[=1000000000] $#,###,, "Billion";[=1000000]$#.#,, "Mil"; $#, "k"

HTH,

Conan




"coastal" wrote in message
...
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?

--
--coastal







  #7   Report Post  
Old February 7th 08, 11:50 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 25
Default Custom Number Formats - Billion Dollars Rounded

That worked great!
--
--coastal


"Conan Kelly" wrote:

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



"Conan Kelly" wrote in message
...
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





"Conan Kelly" wrote in message
...
coastal,

How about this?

[=1000000000] $#,###,, "Billion";[=1000000]$#.#,, "Mil"; $#, "k"

HTH,

Conan




"coastal" wrote in message
...
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?

--
--coastal







  #8   Report Post  
Old February 7th 08, 11:51 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 25
Default Custom Number Formats - Billion Dollars Rounded

Thanks!
--
--coastal


"Tyro" wrote:

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

"Tyro" wrote in message
...
[=1000000000] $#,###,, "Billion";[=1000000]$#0.0,, "Mil"; $#, "k"

"coastal" wrote in message
...
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?

--
--coastal






  #9   Report Post  
Old June 10th 13, 04:29 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2013
Posts: 2
Default Custom Number Formats - Billion Dollars Rounded

On Thursday, February 7, 2008 4:55:20 PM UTC-5, Conan Kelly wrote:
coastal,

How about this?

[=1000000000] $#,###,, "Billion";[=1000000]$#.#,, "Mil"; $#, "k"

HTH,

Conan




"coastal" wrote in message
...
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?

--
--coastal


Thank you for this...!!!

google people are smart people!
  #10   Report Post  
Old June 10th 13, 04:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2013
Posts: 2
Default Custom Number Formats - Billion Dollars Rounded

Thanks for this!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Number Formats Dickie Worton Excel Discussion (Misc queries) 1 February 5th 08 04:32 PM
custom number formats Dave F Excel Discussion (Misc queries) 3 October 7th 06 12:28 AM
custom number formats Louise Excel Worksheet Functions 7 May 4th 06 12:30 PM
[$-409] in Custom Number Formats Conan Kelly Charts and Charting in Excel 2 April 14th 06 12:19 PM
Custom Number Formats Dan Swartz Excel Worksheet Functions 1 September 24th 05 11:09 PM


All times are GMT +1. The time now is 11:02 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017