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

=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

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

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

[=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