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

                                          Google Spreadsheet   Post  #865
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 09, 2012
user cboteros said : (http://productforums.google.com/forum/?zx=sa5238gcjnz7#!searchin/docs/cboteros/docs/yVyfQZ1IHtY/BBqRyARx5qYJ)
Add formula fields to form entries (with ArrayFormula and GoogleFinance) 

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:

Spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0Ar9zueSgCuIydFdRQ3IzMHQ0b3BVMXZKQlhxQUt3bXc

Form: https://docs.google.com/spreadsheet/viewform?formkey=dFdRQ3IzMHQ0b3BVMXZKQlhxQUt3bXc6MQ&ifq

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

F2:=arrayformula(IF(C2:C="USD";1;GoogleFinance("currency:"&C2:C&"USD")))

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": 

H3:=arrayformula(IF(C3:C="USD";1;GoogleFinance("currency:"&C3:C&"usd")))

My guess is that there is something in the way the formula GoogleFinance works that I'm not understanding and so this the Arrayformula is not working properly for me in this case... any idea of what that could be?

BTW, I'm aware that the currency exchanges for GoogleFinances are just for reference and can't be taken seriously, but I just need them for that... as a reference.

Thank you!

Carlos

------------------------------------------------------------------------------------------

As of now (Nov-09-2012) GoogleFinance function can not be used to operate on an array of values -- so in the following I present a WorkAround solution wherein I have used a set of helper columns