I've looked around for how to format google finance results and I can't seem to find an easy answer. I want to output weekly CX averages for USD to CAD.
Google finance outputs a 4-cell response, I simply want the single cell weekly average.. Example below:
Forumula:
=GOOGLEFINANCE("CURRENCY:USDCAD", "price", "01/01/2022", 7, "WEEKLY")
Output vs. desired
You can use the INDEX() function to return the content of a specific cell from your data, specified by row and column offset. If your data is dynamic and you want to get the last cell you can use ROWS() and COLUMNS() which both returns the number of rows and columns from your range together with INDEX.
=index(GOOGLEFINANCE("CURRENCY:USDCAD", "price", "01/01/2022", 7, "WEEKLY"),ROWS(GOOGLEFINANCE("CURRENCY:USDCAD", "price", "01/01/2022", 7, "WEEKLY")),COLUMNS(GOOGLEFINANCE("CURRENCY:USDCAD", "price", "01/01/2022", 7, "WEEKLY")))
Otherwise if the data is fixed and you know specifically which row and column you want to get you can just set the offset specifically (as stated by mohagali in the comment):
=index(GOOGLEFINANCE("CURRENCY:USDCAD", "price", "01/01/2022", 7, "WEEKLY"),2,2)
Result:
References:
