Skip to content

Instantly share code, notes, and snippets.

@aquarion aquarion/gist:4619921
Last active Dec 11, 2015

Embed
What would you like to do?
=if( // #IsPounds:
iferror( // #FindPoundSign
find("£",TO_TEXT(E2), // This will error out if it isn't there.
1) // #IsPounds:True (returned by Find)
,0 // #IsPounds:No (returned by iferror)
), // End iferror#FindPoundSign
// This is the result if #IsPounds:True
E2, // return it in the cell. #GAMEOVER
// if #IsPounds:False:
( // #GetCurrencyConversion:
iferror( // the GoogleFinance/Index will error out if the day wasn't a trading day #TradeDataExists:
// Asked for historic information (Date is D2) GoogleFinance will return a 2x2 array,
// which we only want one cell of, Index(range,X,Y) returns that as the correct multiplier.
Index(GoogleFinance("CURRENCY:USDGBP", ,D2),2,2), // #TradeDataExists:Sucess Set to historic data (Did not error)
GoogleFinance("CURRENCY:USDGBP") // #TradeDataExists:Failed Set to today's data
) // End ifError#TradeDataExists
) // End #GetCurrencyConversion
* E2 // result of #GetCurrencyConversion * Cost in Dollars returned. #GAMEOVER
) // end if#isPounds
=if(iferror(find("£",TO_TEXT(E2), 1),0), E2, iferror(Index(GoogleFinance("CURRENCY:USDGBP", ,D2),2,2) * E2,GoogleFinance("CURRENCY:USDGBP")))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.