google-sheetsgoogle-sheets-formulacurrencygoogle-financecurrency-exchange-rates

Use GoogleFinance Currency Conversion on Sum

I'm trying to use GoogleFinance currency conversion on a sum but can't get the correct format. My cell is as follows:

 =SUM(((IMPORTRANGE("sheet-key","H2:H300"))/100*40)+(IMPORTRANGE("sheet-key","H2:H300")))

I want the results of the sum to be then converted from GBP into USD. I tried adding &GoogleFinance("CURRENCY:GBPUSD") to the end but that's obviously wrong. I can get it to work if I put the conversion in an adjacent cell but that's not what I want.

Solution

  • To begin with, just making sure that you understand the IMPORTRANGE function. Usually this function yields an array of data (in your case 299 values). But in your case this formula fails to yield the array. If the given formula...

    =SUM(((IMPORTRANGE("sheet-key","H2:H300"))/100*40)+(IMPORTRANGE("sheet-key","H2:H300")))

    ...works, then you get only the result of the first cell (H2), i.e. just this: H2/100*40+H2. All the remaining cells (H3:H300) are not processed. Do you really want that? If so, then OK, just proceed to the solution.

    If you actually want the whole range (H2:H300) to get processed you should use this formula:

    =SUM(ARRAYFORMULA(((IMPORTRANGE("sheet-key","H2:H300"))/100*40)),ARRAYFORMULA(IMPORTRANGE("sheet-key","H2:H300")))

    The solution.

    To do a conversion you should multiply your result by the currency conversion rate using * symbol:

    *GoogleFinance("CURRENCY:GBPUSD")

    as opposed to

    &GoogleFinance("CURRENCY:GBPUSD")

    Note: GoogleFinance() is ever updating.