Google Sheets: Convert a master currency into a local currency on specific Sheets

Question

I have a master budget spreadsheet that is in one currency (NZD).

I then use =QUERY('Sheet',"Select * where (A <> '' and B = 'UK')", 0) and other variations to populate each line item into a regional budget.

I would like the regional budgets to be in their local currency. In the example above GBP.

I have quickly researched query + format, but I'm not sure this will work for me.

Is there any way to do this?

Sheet Here: https://docs.google.com/spreadsheets/d/1aUasL1Y19SJ03ATiRpFYmPDIJOW9t-LxsHa3oNJL9iA/edit?usp=sharing

Answer 1

It would be much easier if your shared sheet was editable, which helps people provide you an answer. However, this formula may help you.

The first thing you probably want is the GOOGLEFINANCE formula to get the exchange rate. You can put this anywhere in your sheet, but I'm assuming it is placed in E1, as follows:

=GOOGLEFINANCE("Currency:NZDGBP" )

If you had many currencies you were dealing with you could automate building the currency string, but I won't add that here.

Then to mimic your existing query in A6, try this formula, putting it in A7:

=QUERY(Global!6:9, 
  "Select A, B, C*" & $E$1 & ", D*" & $E$1 & ", E*" & $E$1 & " 
   where (A <> '' and B ='UK') 
   label C*" & $E$1 & " '', D*" & $E$1 & " '', E*" & $E$1 & " '' ",0)

Also be sure to format row 7 as British pound currency. It would be possible to force the pound sign in the fomula, but the result would be text strings, and so unable to be used for further calculations.

This looks quite messy, but most of that is removing headers, since we are doing multiplication, using the exchange rate in E1.

We select column A, column B, column C times the exchange rate, etc..

There are many other ways to do this depending on how you want to structure your sheet, but this most closely replicates what you were already doing.

Note that this uses a live exchange rate, so your budget amounts could change over time. If necessary put a fixed exchange rate in cell E1 (or wherever you prefer.)

Let me know if you have any questions, or if this helps.

Answer 2

try:

=ARRAYFORMULA(QUERY({Global!A:B, Global!C:E*GOOGLEFINANCE("currency:NZDGBP")}, 
 "where Col1 != '' 
    and Col2 = 'UK' 
  format Col3 '£0', 
         Col4 '£0,000', 
         Col5 '£0,000.00'", 0))

(select format you wish)