Learn Excel from MrExcel – “Convert Various Currencies”: Podcast #1659

Learn Excel from MrExcel – “Convert Various Currencies”: Podcast #1659


MrExcel podcast is sponsored by
Easy-XL. Learn Excel from MrExcel podcast,
episode: 1659, Convert Various Currencies. Hey! Welcome back to the MrExcel netcast. I’m Bill Jelen. Yesterday,
we answered a question from Bob. Bob was trying to Auto Filter a table and put in different values and
the formula AUTOCOMPLETE was or the AUTOCOMPLETE for the table
was driving him crazy. You know, a better way to go completely better way to go. First of all, let’s make this into a table
again with Ctrl+T. Click OK. That way our
AUTOCOMPLETE will work. We already had this little table up here that converts GBP to,
this is the conversion rate. We have to multiply the currency amount
by this to convert to GBP. So we can add a new column here GBP for Great Britain Pounds, all right. Equal, this amount, times the VLOOKUP. VLOOKUP stands for Vertical Lookup. WE Wanna look up that currency code,
comma, in this table here and I want to lock that down. So I’m going to press F4, comma. We want the second column, So two and we want exact matches, so we’re going to put a False at the end. All right, so the amount times
the rate here in the lookup table. We’re getting the second column
from the lookup table. I’ll press Enter
and we actually want Excel to copy that down automatically. And so it’s just a little test here. €2083, comes up with 1799. I do equal 2083, times .864. 1799, all right, so there’s a single formula that gets copied down and
solves the problem, all the way down. Yeah, as I look at this point 408, I’m going
to add one more thing in here. I bet we want to round this off
to the nearest either dollar or whatever there, I don’t know!
Is it’s cents? I don’t know. and we will copy that formula down. All right, so there we go.
I’ve the single formula that will go out to this lookup table, figure out the currency conversion rate and then tomorrow you know,
if the rates change, if I have to do this some other day, I just come here and you know,
update the new value like .62 and you
see that the relevant cells, The relevant cells
will automatically update So this VLOOKUP formula,
a little bit more straightforward than using the filter
in four different formulas that Bob was trying to do yesterday
but I understand. Yeah! There’s five ways
to skin a cat in Excel and sometimes you know,
VLOOKUP is eluding you. So the the other way
certainly, would’ve worked All right! Hey, I wanna thank you for stopping by. We’ll see you next time
for another netcast from MrExcel.

5 Comments

  • งูพิษ คิดบวก

    March 5, 2013

    Please let me know.
    I want to know.
    When we want to find the sum between the columns.
    Ex. Column A,B,C,D,E,F,G,H….ZZ
    To find the total column A,C,E,G,….
    Without having to select each cell.

    I tried the formula.
    =Sumif(A1:OJ1),"Name",(B1:OJ1)

    But..It can not be used.
    I don't Know

    Thank you.

    Reply
  • ExcelStrategy

    March 5, 2013

    The "ROUND" function and the "VLOOKUP" are the pillars of Excel !

    Reply
  • David B

    March 6, 2013

    I thought of a single equation as well, but did nestled IF statements. Forgot all about V Lookup

    Reply
  • Juanita Giles

    March 15, 2013

    If you name you ranges, ie Jan, Feb, Mar, Apr etc. Then try just the "SUM" function. Ex: =Sum(Jan, Mar) to Sum together multiple non-continuous columns.

    Reply
  • srinivasarao lella

    November 28, 2014

    thank you Bill Jelen ,its very helpble to me

    Reply

Leave a Reply