If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help needed with IF function
Hi All,
I am trying to come up with a spread sheet that would convert amount in certain foreign currency to local currency equivalent and show final value in local currency only if there are input for both foreign currency (e.g.USD, EUR) and amount in 2 separate adjacent cells. The output in another cell for the local currency equivalent should not display any thing if both datas are NOT present. Think the nested IF function combined with VLookup can do this trick. So far, I have only managed below Vlookup formula and the cell display #NA which is not desirable. I would like it to be blank. Just couldn't work out the correct nested IF to combine into below Vlookup. =VLOOKUP('Work Sheet'!F4,'Currency Exchange Rating'!A15,2) In sheet Currency Exchange Rate are the existing Exchange rate which is updated weekly. The results are calculated and displayed in Work Sheet. Any help would appreciated here ... Thnks in advance. |
#2
|
|||
|
|||
If you wan't to avoid the N/A during you're lookup and
only give an answer when the lookup finds something... =if(isna(VLOOKUP('Work Sheet'!F4,'Currency Exchange Rating'!A15,2)),"",VLOOKUP('Work Sheet'!F4,'Currency Exchange Rating'!A15,2)) this gives you a blank when there is nothing to lookup - you can change the "" to 0 if you'd rather have it display a zero. hth, Dave -----Original Message----- Hi All, I am trying to come up with a spread sheet that would convert amount in certain foreign currency to local currency equivalent and show final value in local currency only if there are input for both foreign currency (e.g.USD, EUR) and amount in 2 separate adjacent cells. The output in another cell for the local currency equivalent should not display any thing if both datas are NOT present. Think the nested IF function combined with VLookup can do this trick. So far, I have only managed below Vlookup formula and the cell display #NA which is not desirable. I would like it to be blank. Just couldn't work out the correct nested IF to combine into below Vlookup. =VLOOKUP('Work Sheet'!F4,'Currency Exchange Rating'! A15,2) In sheet Currency Exchange Rate are the existing Exchange rate which is updated weekly. The results are calculated and displayed in Work Sheet. Any help would appreciated here ... Thnks in advance. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
What if the back-end "moves"? | John S. Ford, MD | General Discussion | 13 | November 15th, 2004 09:33 PM |
Help needed with IF function | Adrian Jones | Setting up and Configuration | 1 | November 7th, 2004 12:19 AM |
Function needed | Jez | Worksheet Functions | 0 | October 19th, 2003 08:34 PM |