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
|