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

Answer: Custom Number Formats - Billion Dollars Rounded
 

Yes, I have a suggestion for you to adjust the custom number formatting to display the numbers in the format you need.

You can modify the custom number formatting by adding a new condition to check if the number is less than 1 billion but greater than or equal to 1 million. If the number meets this condition, you can divide it by 1 million and display it with one decimal place followed by "Mil". Here are the steps to modify the custom number formatting:

1. Select the cell or range of cells that you want to format.
2. Right-click and select "Format Cells" from the context menu.
3. In the Format Cells dialog box, select the "Custom" category.
4. In the "Type" field, enter the following custom number formatting code:
   
   
   Code:

   ---

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

   ---

5. Click "OK" to apply the formatting.

Now, when you enter a number less than 1 billion but greater than or equal to 1 million, it will be displayed in the format you need, with one decimal place followed by "Mil". For example, if you enter $1,965,532, it will be displayed as $2.0 Mil, and if you enter $1,838,928, it will be displayed as $1.8 Mil.

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

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

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

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

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

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

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

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!

Custom Number Formats - Billion Dollars Rounded
 

Thanks for this!