Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Currency format
Hello,
Please help me. In a spreadsheet I need to assign the currency that we receive money in. There is a column that states the currency in a 3 letter code. The amounts are downloaded from a CSV file. Here is the fomula that I am using. =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$ #,##0.00_)"),IF($E51="EUR",TEXT($G51,"ぎ #,##0.00_)"),""))) The problem with this formula is that it converts the amount to text. How can I get this or is there another formula that will give me the answer in the relevant currency, as a value with the relevant currency, without having to set the format using the "Cell Format" function? Thank you for your help. Max |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Currency format
There are many ways using Text or Value functions but I prefer the easiest
one as follows: Any simple arithmetic calculation on text will convert them to numbers so add a zero to the formula (+0) and it will turn to number. -- Rasoul Khoshravan Azar Kobe University, Kobe, Japan "Max" wrote: Hello, Please help me. In a spreadsheet I need to assign the currency that we receive money in. There is a column that states the currency in a 3 letter code. The amounts are downloaded from a CSV file. Here is the fomula that I am using. =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$ #,##0.00_)"),IF($E51="EUR",TEXT($G51,"ぎ #,##0.00_)"),""))) The problem with this formula is that it converts the amount to text. How can I get this or is there another formula that will give me the answer in the relevant currency, as a value with the relevant currency, without having to set the format using the "Cell Format" function? Thank you for your help. Max |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Currency format
What about the currency format?
"Khoshravan" wrote: There are many ways using Text or Value functions but I prefer the easiest one as follows: Any simple arithmetic calculation on text will convert them to numbers so add a zero to the formula (+0) and it will turn to number. -- Rasoul Khoshravan Azar Kobe University, Kobe, Japan "Max" wrote: Hello, Please help me. In a spreadsheet I need to assign the currency that we receive money in. There is a column that states the currency in a 3 letter code. The amounts are downloaded from a CSV file. Here is the fomula that I am using. =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$ #,##0.00_)"),IF($E51="EUR",TEXT($G51,"ぎ #,##0.00_)"),""))) The problem with this formula is that it converts the amount to text. How can I get this or is there another formula that will give me the answer in the relevant currency, as a value with the relevant currency, without having to set the format using the "Cell Format" function? Thank you for your help. Max |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Currency format
Using your present formula, say in Column H, is displaying the correct
format, according to the contents of Column E. You can add these "text" values, as long as you *don't* use Sum(). For example: =H51+H52+H53+H54 ... etc. And, of course, to format *that* totaling cell, for example: =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Max" wrote in message ... Hello, Please help me. In a spreadsheet I need to assign the currency that we receive money in. There is a column that states the currency in a 3 letter code. The amounts are downloaded from a CSV file. Here is the fomula that I am using. =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$ #,##0.00_)"),IF($E51="EUR",TEXT($G51,"ぎ #,##0.00_)"),""))) The problem with this formula is that it converts the amount to text. How can I get this or is there another formula that will give me the answer in the relevant currency, as a value with the relevant currency, without having to set the format using the "Cell Format" function? Thank you for your help. Max |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Currency format
Checking back, I don't think that my suggestion will work for adding those
"text" cells, unless the regional settings recognize the currency symbol. Checking with USD ($) works for me. The only way I could get the ぎ to total, was to change my regionals. Sorry! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... Using your present formula, say in Column H, is displaying the correct format, according to the contents of Column E. You can add these "text" values, as long as you *don't* use Sum(). For example: =H51+H52+H53+H54 ... etc. And, of course, to format *that* totaling cell, for example: =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)") -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Max" wrote in message ... Hello, Please help me. In a spreadsheet I need to assign the currency that we receive money in. There is a column that states the currency in a 3 letter code. The amounts are downloaded from a CSV file. Here is the fomula that I am using. =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$ #,##0.00_)"),IF($E51="EUR",TEXT($G51,"ぎ #,##0.00_)"),""))) The problem with this formula is that it converts the amount to text. How can I get this or is there another formula that will give me the answer in the relevant currency, as a value with the relevant currency, without having to set the format using the "Cell Format" function? Thank you for your help. Max |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Currency format
Hello Ragdryer,
Thank you for your effort. If you try the Function "Fixed" under the Text formula selection it will give you the Currency selection that your computer is set at. I guess its back to the drawing board for me. Thank you again. Max "Ragdyer" wrote: Checking back, I don't think that my suggestion will work for adding those "text" cells, unless the regional settings recognize the currency symbol. Checking with USD ($) works for me. The only way I could get the ぎ to total, was to change my regionals. Sorry! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... Using your present formula, say in Column H, is displaying the correct format, according to the contents of Column E. You can add these "text" values, as long as you *don't* use Sum(). For example: =H51+H52+H53+H54 ... etc. And, of course, to format *that* totaling cell, for example: =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)") -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Max" wrote in message ... Hello, Please help me. In a spreadsheet I need to assign the currency that we receive money in. There is a column that states the currency in a 3 letter code. The amounts are downloaded from a CSV file. Here is the fomula that I am using. =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$ #,##0.00_)"),IF($E51="EUR",TEXT($G51,"ぎ #,##0.00_)"),""))) The problem with this formula is that it converts the amount to text. How can I get this or is there another formula that will give me the answer in the relevant currency, as a value with the relevant currency, without having to set the format using the "Cell Format" function? Thank you for your help. Max |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Currency format
If you can use a macro solution it would be pretty easy, I think.
__________________________________________________ _______________________ "Max" wrote in message ... Hello Ragdryer, Thank you for your effort. If you try the Function "Fixed" under the Text formula selection it will give you the Currency selection that your computer is set at. I guess its back to the drawing board for me. Thank you again. Max "Ragdyer" wrote: Checking back, I don't think that my suggestion will work for adding those "text" cells, unless the regional settings recognize the currency symbol. Checking with USD ($) works for me. The only way I could get the ? to total, was to change my regionals. Sorry! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... Using your present formula, say in Column H, is displaying the correct format, according to the contents of Column E. You can add these "text" values, as long as you *don't* use Sum(). For example: =H51+H52+H53+H54 ... etc. And, of course, to format *that* totaling cell, for example: =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)") -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Max" wrote in message ... Hello, Please help me. In a spreadsheet I need to assign the currency that we receive money in. There is a column that states the currency in a 3 letter code. The amounts are downloaded from a CSV file. Here is the fomula that I am using. =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$ #,##0.00_)"),IF($E51="EUR",TEXT($G51,"? #,##0.00_)"),""))) The problem with this formula is that it converts the amount to text. How can I get this or is there another formula that will give me the answer in the relevant currency, as a value with the relevant currency, without having to set the format using the "Cell Format" function? Thank you for your help. Max |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Currency format
Hello Vasant,
Thank you for you effort, I am sure using a macro will be the only way. Thank you and best regards Max "Vasant Nanavati" wrote: If you can use a macro solution it would be pretty easy, I think. __________________________________________________ _______________________ "Max" wrote in message ... Hello Ragdryer, Thank you for your effort. If you try the Function "Fixed" under the Text formula selection it will give you the Currency selection that your computer is set at. I guess its back to the drawing board for me. Thank you again. Max "Ragdyer" wrote: Checking back, I don't think that my suggestion will work for adding those "text" cells, unless the regional settings recognize the currency symbol. Checking with USD ($) works for me. The only way I could get the ? to total, was to change my regionals. Sorry! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... Using your present formula, say in Column H, is displaying the correct format, according to the contents of Column E. You can add these "text" values, as long as you *don't* use Sum(). For example: =H51+H52+H53+H54 ... etc. And, of course, to format *that* totaling cell, for example: =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)") -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Max" wrote in message ... Hello, Please help me. In a spreadsheet I need to assign the currency that we receive money in. There is a column that states the currency in a 3 letter code. The amounts are downloaded from a CSV file. Here is the fomula that I am using. =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$ #,##0.00_)"),IF($E51="EUR",TEXT($G51,"? #,##0.00_)"),""))) The problem with this formula is that it converts the amount to text. How can I get this or is there another formula that will give me the answer in the relevant currency, as a value with the relevant currency, without having to set the format using the "Cell Format" function? Thank you for your help. Max |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Currency format
Hello Ragdryer,
I made a mistake the function is not "Fixed"it is "Dollar". Thanks Max "Max" wrote: Hello Ragdryer, Thank you for your effort. If you try the Function "Fixed" under the Text formula selection it will give you the Currency selection that your computer is set at. I guess its back to the drawing board for me. Thank you again. Max "Ragdyer" wrote: Checking back, I don't think that my suggestion will work for adding those "text" cells, unless the regional settings recognize the currency symbol. Checking with USD ($) works for me. The only way I could get the ぎ to total, was to change my regionals. Sorry! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... Using your present formula, say in Column H, is displaying the correct format, according to the contents of Column E. You can add these "text" values, as long as you *don't* use Sum(). For example: =H51+H52+H53+H54 ... etc. And, of course, to format *that* totaling cell, for example: =TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)") -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Max" wrote in message ... Hello, Please help me. In a spreadsheet I need to assign the currency that we receive money in. There is a column that states the currency in a 3 letter code. The amounts are downloaded from a CSV file. Here is the fomula that I am using. =IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$ #,##0.00_)"),IF($E51="EUR",TEXT($G51,"ぎ #,##0.00_)"),""))) The problem with this formula is that it converts the amount to text. How can I get this or is there another formula that will give me the answer in the relevant currency, as a value with the relevant currency, without having to set the format using the "Cell Format" function? Thank you for your help. Max |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Currency format | Excel Discussion (Misc queries) | |||
format cells for currency 2450 and have it format to $24.50? | Excel Worksheet Functions | |||
Change General Format to Currency Format | Excel Worksheet Functions | |||
Currency format | Excel Worksheet Functions | |||
why does currency format change to number format? | Excel Discussion (Misc queries) |