Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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". |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenated formula help | Excel Worksheet Functions | |||
Format part of text in concatenated field | Excel Worksheet Functions | |||
appearance of a concatenated value | Excel Worksheet Functions | |||
COMPARE CURRENT MORTAGAGE COSTS TO NEW MORTAGAGE COSTS | Excel Discussion (Misc queries) | |||
COMPARE CURRENT MORTAGAGE COSTS TO NEW MORTAGAGE COSTS | Excel Discussion (Misc queries) |