Rounding Of Concatenated Costs Field
 

The second field in my concatenate function is actual costs, the first is
line of business. One value, for example, is $29,501,341.16 which I can round
down to $29,501,341 but the concatenated value is fully exploded with the
decimals. What I ideally would like to do is have the concatenated value read
$29.5M. Any ideas? Excel 2007

Rounding Of Concatenated Costs Field
 

You should've posted your formula.

Try this:

A1 = $29,501,341.16

=TEXT(A1/1000000,"$0.0\M")

--
Biff
Microsoft Excel MVP


"Caldo" wrote in message
...
The second field in my concatenate function is actual costs, the first is
line of business. One value, for example, is $29,501,341.16 which I can
round
down to $29,501,341 but the concatenated value is fully exploded with the
decimals. What I ideally would like to do is have the concatenated value
read
$29.5M. Any ideas? Excel 2007

Rounding Of Concatenated Costs Field
 

=DOLLAR(A1/(10^6),1)&"M"


"Caldo" wrote:

The second field in my concatenate function is actual costs, the first is
line of business. One value, for example, is $29,501,341.16 which I can round
down to $29,501,341 but the concatenated value is fully exploded with the
decimals. What I ideally would like to do is have the concatenated value read
$29.5M. Any ideas? Excel 2007

Rounding Of Concatenated Costs Field
 

"Caldo" wrote:
The second field in my concatenate function is actual costs, the first is
line of business. One value, for example, is $29,501,341.16 which I can
round
down to $29,501,341 but the concatenated value is fully exploded with the
decimals. What I ideally would like to do is have the concatenated value
read
$29.5M. Any ideas? Excel 2007

I have no idea what you are concatenated and why, since you neglected to
post your formula.

If you are merely concatenating a number and "M" (e.g. =A1/1000000&"M"), you
can dispense with dividing by 1000000 and concatenating "M", and instead use
the following custom format "$0.0,,\M" without the quotes.

But if you still need concatenation for some other purpose (e.g. =A1&"
total"), you can apply the same idea using the TEXT function, for example:
=text(A1,"$0.0,,\M")&" total".

Rounding Of Concatenated Costs Field
 

Hi,

I don't follow this concatenation thing but if the number in the cell is a
number then create the following custom format:

$0.0,,\M

To do this choose Format, Cells, Number tab, Custom, and enter the above on
the Type line.

If you want to use a formula approach then

=TEXT(A1,"$0.0,,""M""")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Caldo" wrote:

The second field in my concatenate function is actual costs, the first is
line of business. One value, for example, is $29,501,341.16 which I can round
down to $29,501,341 but the concatenated value is fully exploded with the
decimals. What I ideally would like to do is have the concatenated value read
$29.5M. Any ideas? Excel 2007

Rounding Of Concatenated Costs Field
 

Hi,

I never get to use that function - nice.

You can shorten it to

=DOLLAR(D6/10^6,1)&"M"

because order of presidence is ^ before * or /.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Teethless mama" wrote:

=DOLLAR(A1/(10^6),1)&"M"


"Caldo" wrote:

The second field in my concatenate function is actual costs, the first is
line of business. One value, for example, is $29,501,341.16 which I can round
down to $29,501,341 but the concatenated value is fully exploded with the
decimals. What I ideally would like to do is have the concatenated value read
$29.5M. Any ideas? Excel 2007

Rounding Of Concatenated Costs Field
 

Or you can use the same pattern in the TEXT function that you proposed for
the Custom Format...

=TEXT(A21,"$0.0,,\M")

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

I don't follow this concatenation thing but if the number in the cell is a
number then create the following custom format:

$0.0,,\M

To do this choose Format, Cells, Number tab, Custom, and enter the above
on
the Type line.

If you want to use a formula approach then

=TEXT(A1,"$0.0,,""M""")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Caldo" wrote:

The second field in my concatenate function is actual costs, the first is
line of business. One value, for example, is $29,501,341.16 which I can
round
down to $29,501,341 but the concatenated value is fully exploded with the
decimals. What I ideally would like to do is have the concatenated value
read
$29.5M. Any ideas? Excel 2007

Rounding Of Concatenated Costs Field
 

Here is the current formula:

=CONCATENATE(G6, " ","(", "$",J6/(10^6),")")

which returns this

Administration ($27.58053056)

How can I get it to round to $29.6M

"Rick Rothstein" wrote:

Or you can use the same pattern in the TEXT function that you proposed for
the Custom Format...

=TEXT(A21,"$0.0,,\M")

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

I don't follow this concatenation thing but if the number in the cell is a
number then create the following custom format:

$0.0,,\M

To do this choose Format, Cells, Number tab, Custom, and enter the above
on
the Type line.

If you want to use a formula approach then

=TEXT(A1,"$0.0,,""M""")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Caldo" wrote:

The second field in my concatenate function is actual costs, the first is
line of business. One value, for example, is $29,501,341.16 which I can
round
down to $29,501,341 but the concatenated value is fully exploded with the
decimals. What I ideally would like to do is have the concatenated value
read
$29.5M. Any ideas? Excel 2007

Rounding Of Concatenated Costs Field
 

Administration ($27.58053056)
How can I get it to round to $29.6M

You can get it rounded to $27.6M...

Using Ricks method:

=G6&" "&TEXT(J6,"($0.0,,\M)")


--
Biff
Microsoft Excel MVP


"Caldo" wrote in message
...
Here is the current formula:

=CONCATENATE(G6, " ","(", "$",J6/(10^6),")")

which returns this

Administration ($27.58053056)

How can I get it to round to $29.6M

"Rick Rothstein" wrote:

Or you can use the same pattern in the TEXT function that you proposed
for
the Custom Format...

=TEXT(A21,"$0.0,,\M")

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

I don't follow this concatenation thing but if the number in the cell
is a
number then create the following custom format:

$0.0,,\M

To do this choose Format, Cells, Number tab, Custom, and enter the
above
on
the Type line.

If you want to use a formula approach then

=TEXT(A1,"$0.0,,""M""")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Caldo" wrote:

The second field in my concatenate function is actual costs, the first
is
line of business. One value, for example, is $29,501,341.16 which I
can
round
down to $29,501,341 but the concatenated value is fully exploded with
the
decimals. What I ideally would like to do is have the concatenated
value
read
$29.5M. Any ideas? Excel 2007

Rounding Of Concatenated Costs Field
 

You don't need the CONCATENATE function to put text together, you can link
them with an & character. Also, since we are using the TEXT function, we can
put your space and parentheses right inside the TEXT function's pattern
string. This should do what you want...

=G6&TEXT(J6," ($0.0,,\M)")

--
Rick (MVP - Excel)


"Caldo" wrote in message
...
Here is the current formula:

=CONCATENATE(G6, " ","(", "$",J6/(10^6),")")

which returns this

Administration ($27.58053056)

How can I get it to round to $29.6M

"Rick Rothstein" wrote:

Or you can use the same pattern in the TEXT function that you proposed
for
the Custom Format...

=TEXT(A21,"$0.0,,\M")

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

I don't follow this concatenation thing but if the number in the cell
is a
number then create the following custom format:

$0.0,,\M

To do this choose Format, Cells, Number tab, Custom, and enter the
above
on
the Type line.

If you want to use a formula approach then

=TEXT(A1,"$0.0,,""M""")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Caldo" wrote:

The second field in my concatenate function is actual costs, the first
is
line of business. One value, for example, is $29,501,341.16 which I
can
round
down to $29,501,341 but the concatenated value is fully exploded with
the
decimals. What I ideally would like to do is have the concatenated
value
read
$29.5M. Any ideas? Excel 2007

Rounding Of Concatenated Costs Field
 

That got it. Thanks for the help

"Rick Rothstein" wrote:

You don't need the CONCATENATE function to put text together, you can link
them with an & character. Also, since we are using the TEXT function, we can
put your space and parentheses right inside the TEXT function's pattern
string. This should do what you want...

=G6&TEXT(J6," ($0.0,,\M)")

--
Rick (MVP - Excel)


"Caldo" wrote in message
...
Here is the current formula:

=CONCATENATE(G6, " ","(", "$",J6/(10^6),")")

which returns this

Administration ($27.58053056)

How can I get it to round to $29.6M

"Rick Rothstein" wrote:

Or you can use the same pattern in the TEXT function that you proposed
for
the Custom Format...

=TEXT(A21,"$0.0,,\M")

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

I don't follow this concatenation thing but if the number in the cell
is a
number then create the following custom format:

$0.0,,\M

To do this choose Format, Cells, Number tab, Custom, and enter the
above
on
the Type line.

If you want to use a formula approach then

=TEXT(A1,"$0.0,,""M""")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Caldo" wrote:

The second field in my concatenate function is actual costs, the first
is
line of business. One value, for example, is $29,501,341.16 which I
can
round
down to $29,501,341 but the concatenated value is fully exploded with
the
decimals. What I ideally would like to do is have the concatenated
value
read
$29.5M. Any ideas? Excel 2007