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

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

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

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

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

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

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

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

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