Currency Conversion

[Mike Baker]

I have a column of numbers representing financial values prefixed by £, $ or €. I want to create another column with all these values converted to sterling (£) by reference to an exchange rate field. How can I get Excel to recognise the currency prefix and apply the correct exchange rate conversion?

Help please!

 

[MikeWx]

Mike,

This solution uses an old Excel XLM function called GET.CELL that Bill Jelen talks about in his "Excel Gurus Gone Wild" book. To use this function, use the Define Name (Excel 2003) or Name Manager (Excel 2007) to define a new range. I called mine CELLFORMAT. In the Refers to block, type =GET.CELL(53,Sheet1!A1). The 53 is the option that displays a cell as text, including any symbols associated with formatting. In the column next to the various currency values, type the formula shown in the following spreadsheet to perform a VLOOKUP using the currency symbol as the key to a currency conversion chart.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">£10.00</TD><TD style="TEXT-ALIGN: right">£10.00</TD><TD> </TD><TD> </TD><TD>£</TD><TD style="TEXT-ALIGN: right">1.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">$27.00</TD><TD style="TEXT-ALIGN: right">£14.85</TD><TD> </TD><TD> </TD><TD>$</TD><TD style="TEXT-ALIGN: right">0.55</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">€ 12.00</TD><TD style="TEXT-ALIGN: right">£7.80</TD><TD> </TD><TD> </TD><TD>€</TD><TD style="TEXT-ALIGN: right">0.65</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=A1*VLOOKUP(LEFT(CELLFORMAT,1),$E$1:$F$3,2,0)</TD></TR><TR><TD>B2</TD><TD>=A2*VLOOKUP(LEFT(CELLFORMAT,1),$E$1:$F$3,2,0)</TD></TR><TR><TD>B3</TD><TD>=A3*VLOOKUP(LEFT(CELLFORMAT,1),$E$1:$F$3,2,0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Mike Wx

 

[Mike Baker]

Thank you very much - brilliant!

 

[MikeWx]

Your welcome. If you would like more information about get.cell, see the following link:

http://www.mrexcel.com/forum/showthread.php?t=20611

 

[OceanPlanet13X]

Hi Mike

Excellent formula - I have tried your formula, it works for first 4 row then I get # value?

Would you know "why" happens?? - thanks in advance



Value Convert value £ symbol conversion rate
£10 £6 $ 0.56
$15.00 £675 R 45
€ 50.00 £600 € 12
€ 50.00 £500 Ύ 10
¥100.00 #VALUE! £ 15
¥100.00 #VALUE!
€ 50.00 #VALUE! ,=C8*VLOOKUP(LEFT(currency,1),$E$2:$F$6,2,0)

 

[MikeWx]

I see you added another currency type, and apparently added its conversion rate to the range $E$2:$F$6. When I did the same thing to my version, it continued to convert the amounts in new currency type.

The only way I could get the #VALUE error was to concatenate the currency symbol with a currency amount instead of entering the currency as a number and then formatting it to the appropriate currency. As long as the amount is a number and the currency symbol is in the $E$2:$F$6 range, it works for me.