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.202

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

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.010067Then 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:

=IF(B1<>"",C1*(VLOOKUP(B1,$H$1:$I$3,2,0)),"")

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)

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

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)))

No function needed... Select column B

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

thank you very much both of you ..

i think i will use the formula because they wanted to be automaticthanks alot for your effort

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History