Currency and conditional formating

[nikolask7]

Hi,
I am creating and custom purchases statement that i want to record all my purchases. each row is an item purchased. however i have items purchased in USD,EUR and GBP.

In one column i have this information (USD,EUR or GBP). i want the next column with the amount to change to currency format ($,€,£) based on previous cell.

 

[bobsan42]

Hi,
for me the easiest option would be to have a third column with IF statements and TEXT() function to apply the desired format.
something like =IF(A1="USD",TEXT(B1,"$0.00"),IF(A1="EUR",TEXT(B1,"€0.00"),IF(A1="GBP",TEXT(B1,"£0.00"))))
This should work OK for two or three different currencies.

However if you want to be more flexible i would suggest creating a small table wit two columns - currency abbreviation on the left and symbol on the right.
then instead if several IF() statements a VLOOKUP can be used to add the symbol you need. something like:
=TEXT(B1,Vlookup(A1,X1:Y10,2,0)&"0.00")

hope this helps

 

[nikolask7]

This was actually big help. thank you for this

but if i multiply this by cell that contains quantity it does not work. for instance i tried the following
=(IF(A1="USD",TEXT(B1,"$0.00"),IF(A1="EUR",TEXT(B1,"€0.00"),IF(A1="GBP",TEXT(B1,"£0.00")))))*C1
=(IF(A1*C1="USD",TEXT(B1,"$0.00"),IF(A1*C1="EUR",TEXT(B1,"€0.00"),IF(A1*C1="GBP",TEXT(B1,"£0.00")))))

how can i have the same formating effect but being able to multiply this?

 

[bobsan42]

Well sorry bout that the result of TEXT() is a formatted string/text data type - it is not a number. So unless you really want to make some long and complicated formulas to extract the number, i would suggest to use the real number in the previous (source) column B for any further calculations.
For the above formula to work correctly, you must change it like this:
=(IF(A1="USD",TEXT(B1*C1,"$0.00"),IF(A1="EUR",TEXT(B1*C1,"€0.00"),IF(A1="GBP",TEXT(B1*C1,"£0.00")))))

 

[nikolask7]

this is perfect!!! thank you!