| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ! --------- |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ! --------- |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ! --------- |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ! --------- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ! --------- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
| Any way for 2 column vlookups. i.e match last name then match firs | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| When MATCH and v/hLOOKUP functions *FAIL* to match (but they should)... | Excel Worksheet Functions | |||
Linear Mode
