|
Match a currency with its corresponding value
i have 2 worksheets in one excel file
------------------------------------------------------------------------------------------ sheet1: Currency ---------------Amount AUD --------------- 25,000,000 JPY --------------- 85,000,000 EUR --------------- 1,000,000 ------------------------------------------------------------------------------------------ Sheet 2: (Currency Cross-Rate in USD) USD--------------- $1.00 GBP--------------- $2.049194991 EUR--------------- $1.379994037 JPY--------------- $ 0.008185748 AUD ------------- $ 0.880441264 -------------------------------------------------------------------------------------- Question: I need to generate a function which does the following: IF a currency is "EUR" (in sheet1) , the next cell should multiply itself with 1.379994037 (in sheet2). So i have a list of currency and amounts in different currencies. THe functions should automatically match the Currency and multiply it's value. IDEA: if the currency is in JPY, then the Amount in JPY should be multiplied with USD equavelent (which is $0.008185748). All currencies should be in USD-equivelent. DOn't worry, i have my own currency sheet ! Thank you so much ! --------- |
Match a currency with its corresponding value
=B1*VLOOKUP($A1,Sheet2!$A:$B,2,FALSE)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ali" wrote in message ... i have 2 worksheets in one excel file ------------------------------------------------------------------------------------------ sheet1: Currency ---------------Amount AUD --------------- 25,000,000 JPY --------------- 85,000,000 EUR --------------- 1,000,000 ------------------------------------------------------------------------------------------ Sheet 2: (Currency Cross-Rate in USD) USD--------------- $1.00 GBP--------------- $2.049194991 EUR--------------- $1.379994037 JPY--------------- $ 0.008185748 AUD ------------- $ 0.880441264 -------------------------------------------------------------------------------------- Question: I need to generate a function which does the following: IF a currency is "EUR" (in sheet1) , the next cell should multiply itself with 1.379994037 (in sheet2). So i have a list of currency and amounts in different currencies. THe functions should automatically match the Currency and multiply it's value. IDEA: if the currency is in JPY, then the Amount in JPY should be multiplied with USD equavelent (which is $0.008185748). All currencies should be in USD-equivelent. DOn't worry, i have my own currency sheet ! Thank you so much ! --------- |
Match a currency with its corresponding value
Ali,
Put this formula next to your currency amounts on sheet 1 and drag down. =IF(ISNA(B1*(VLOOKUP(A1,Sheet3!A$1:B$5,2,FALSE))), "Conversion factor not found",B1*(VLOOKUP(A1,Sheet3!A$1:B$5,2,FALSE))) If it finds the currency it will perform the multiplication, If not it returns conversion factor not found. Mike "ali" wrote: i have 2 worksheets in one excel file ------------------------------------------------------------------------------------------ sheet1: Currency ---------------Amount AUD --------------- 25,000,000 JPY --------------- 85,000,000 EUR --------------- 1,000,000 ------------------------------------------------------------------------------------------ Sheet 2: (Currency Cross-Rate in USD) USD--------------- $1.00 GBP--------------- $2.049194991 EUR--------------- $1.379994037 JPY--------------- $ 0.008185748 AUD ------------- $ 0.880441264 -------------------------------------------------------------------------------------- Question: I need to generate a function which does the following: IF a currency is "EUR" (in sheet1) , the next cell should multiply itself with 1.379994037 (in sheet2). So i have a list of currency and amounts in different currencies. THe functions should automatically match the Currency and multiply it's value. IDEA: if the currency is in JPY, then the Amount in JPY should be multiplied with USD equavelent (which is $0.008185748). All currencies should be in USD-equivelent. DOn't worry, i have my own currency sheet ! Thank you so much ! --------- |
Match a currency with its corresponding value
THank you so much bob, your formula works perfectly !! Now i can carry on my
work !! Keep it up!! -- --------- "Bob Phillips" wrote: =B1*VLOOKUP($A1,Sheet2!$A:$B,2,FALSE) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ali" wrote in message ... i have 2 worksheets in one excel file ------------------------------------------------------------------------------------------ sheet1: Currency ---------------Amount AUD --------------- 25,000,000 JPY --------------- 85,000,000 EUR --------------- 1,000,000 ------------------------------------------------------------------------------------------ Sheet 2: (Currency Cross-Rate in USD) USD--------------- $1.00 GBP--------------- $2.049194991 EUR--------------- $1.379994037 JPY--------------- $ 0.008185748 AUD ------------- $ 0.880441264 -------------------------------------------------------------------------------------- Question: I need to generate a function which does the following: IF a currency is "EUR" (in sheet1) , the next cell should multiply itself with 1.379994037 (in sheet2). So i have a list of currency and amounts in different currencies. THe functions should automatically match the Currency and multiply it's value. IDEA: if the currency is in JPY, then the Amount in JPY should be multiplied with USD equavelent (which is $0.008185748). All currencies should be in USD-equivelent. DOn't worry, i have my own currency sheet ! Thank you so much ! --------- |
Match a currency with its corresponding value
Mike,
Thank you so much as well. Both yours and Bob's work perfetly, and your formula is very nice , it took me a while to interprete it!! You must be an expert! ! ! Once again, thank you so much ! Keep it up! -- --------- "Mike H" wrote: Ali, Put this formula next to your currency amounts on sheet 1 and drag down. =IF(ISNA(B1*(VLOOKUP(A1,Sheet3!A$1:B$5,2,FALSE))), "Conversion factor not found",B1*(VLOOKUP(A1,Sheet3!A$1:B$5,2,FALSE))) If it finds the currency it will perform the multiplication, If not it returns conversion factor not found. Mike "ali" wrote: i have 2 worksheets in one excel file ------------------------------------------------------------------------------------------ sheet1: Currency ---------------Amount AUD --------------- 25,000,000 JPY --------------- 85,000,000 EUR --------------- 1,000,000 ------------------------------------------------------------------------------------------ Sheet 2: (Currency Cross-Rate in USD) USD--------------- $1.00 GBP--------------- $2.049194991 EUR--------------- $1.379994037 JPY--------------- $ 0.008185748 AUD ------------- $ 0.880441264 -------------------------------------------------------------------------------------- Question: I need to generate a function which does the following: IF a currency is "EUR" (in sheet1) , the next cell should multiply itself with 1.379994037 (in sheet2). So i have a list of currency and amounts in different currencies. THe functions should automatically match the Currency and multiply it's value. IDEA: if the currency is in JPY, then the Amount in JPY should be multiplied with USD equavelent (which is $0.008185748). All currencies should be in USD-equivelent. DOn't worry, i have my own currency sheet ! Thank you so much ! --------- |
| All times are GMT +1. The time now is 04:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com