Excel - convert currency

April 16, 2009 at 10:39:52
Specs: Windows XP
i need to convert each currency to dollars and calculate the amount per msg type as shown in Column A there is Msg type and in Column B there is the Amount with different Currency and some of them are without any amount

Ex :
Msg type Amount
fin.103 AED 7170,
fin.202 USD 1124,86
fin.103 AED 7170,
fin.210 JPY 100000,
fin.300 JPY 100000,
fin.103 AED 7500,
fin.210 USD 250000,
fin.300 USD 250000,

in the Excel i have more than 500 row and i need the following :
1- column C: convert each amount in B to USD (B contain amount with different currency)
2- column E: will have all the msg type
3- column F: calculate the total amount per msg type

hope that someone could help me to solve this problem

See More: Excel - convert currency

Report •

April 16, 2009 at 12:04:32
I'll leave it to you to figure out the correct columns to use for this method...

To convert the currencies, I would build a Lookup table with the currency symbols and the exchange rate as follows...

    H      I
1  AED	0.272257
2  USD	1
3  JPY	0.010067

Then I'd split the amounts in Column B into 2 columns so I could look up the currency symbol in my lookup table.

With the symbol in Column B, the amount (without the symbol) in Column C, I'd put this in Column D:


This would look up the symbol in the table and multiply the number in C1 by the exchange rate. When you update the exchange rate in Column I, the converted number would update also.

To sum the values by message number, I'd use SUMIF:

e.g. =SUMIF(E1:E9,"fin.103",D1:D9)

Report •

April 16, 2009 at 20:51:46
thanks alot for you effort. it's perfect solution.

but is there any function to convert the Currency with amount in B column to be the currency in C and the amount in D and also i need to chancge the (,) to be (.) for calculation

AED 2000,
USD 22740,

it's difficult to segregate the currency and the amount manually for more than 500 rows

Report •

April 17, 2009 at 05:34:36
Currency (assumes there's ALWAYS a space between currency code and value):

=LEFT(B2,SEARCH(" ",B2)-1)

Value (assumes you will ALWAYS have a comma at the end of a line, and there is a space between currency and value):

=VALUE(MID(B2,SEARCH(" ",B2)+1,LEN(B2)-1-SEARCH(" ",B2)))

Report •

Related Solutions

April 17, 2009 at 06:45:11
No function needed...

Select column B

Pulldown Data...Text to Column...Delimited...Next...Check the Space box...Finish

Report •

April 17, 2009 at 07:29:53
thank you very much both of you ..
i think i will use the formula because they wanted to be automatic

thanks alot for your effort

Report •

Ask Question