## Friday, November 9, 2012

### yogi_Workaround For Using Google Finance Formula For Currency Conversion To Operate On an Array of Values

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 09, 2012

I have a Google Spreadsheet which gets data from a Google Form, the data is for currency conversion so I use want to use the formula GoogleFinance("currency:XXXYYY")  where XXX is the currency to exchange from and YYY the currency to exchange to (for example googlefinance("currency:EURUSD") would give the current currency exchange from Euros to American Dollars )

To help with this explanation, I made a test Google Spreasheet with Google Form which you can see in here:

So you can see that in column B is a number which is the amount of money and the column C is the iso representation of the "from" currency (which could be also EUR, CZK, BRL, MXN, and it could also be USD)

So for example, I can have something like:

Amount Currency
147 EUR
197 USD
92 MXN
132 BRL

So here is the non-array formula which I'm using for calculating the currency exchange:
D3:=IF(C3="USD";1;GoogleFinance("currency:"&C3&"USD")) -----> (you will find also all along column D)

I tried to follow the indication in here but without success: http://productforums.google.com/d/msg/docs/RuvdGWeJ-Oc/BfNMkbiOfLkJ

Also something curious is that if I take away the "GoogleFinance" formula and just keep the concatenation of the string, the are being built correctly:

G2:=arrayformula(IF(C2:C="USD";1;"currency:"&C2:C&"usd"))

Also if I add this formula since the 3rd row it "seems" to work, but not really. The problem with this formula is that the calculations with GoogleFinance are done only with the value of "C3":