1

I'm having an issue with the following formula in google sheets:

=IFS(F2<> "EUR", E2*GOOGLEFINANCE("Currency:"&F2&$G$1,"price", H2), F2 = "EUR", E2)

H2 is a cell which holds a date. I am trying to get a formula I can use for multiple different dates. I got the following error message:

Function MULTIPLY parameter 2 expects number values. But 'Date' is a text and cannot be coerced to a number.

According to my research the formula has these constraints:

=GOOGLEFINANCE("Currency:USDGBP", "price", DATE(YYYY,MM,DD), DATE(YYYY,MM,DD)

Where the first nested DATE function is the start date, and the second DATE function is the end date. And they are optional.

I tried Date(H2) and I got this error message:

Wrong number of arguments to DATE. Expected 3 arguments, but received 1 arguments.

Thank you in advance!

Chiara
  • 63
  • 6

2 Answers2

2

any time GoogleFinance() reutrns a historical array, you need to INDEX() it to get just the single answer.

It's almost always the second row and second column of the array that you want.

So:

=INDEX(Goooglefinance(.... ), 2, 2)
MattKing
  • 7,373
  • 8
  • 13
  • Does this work for anybody? If I try to index into the results of googlefinance with indices 2,2 I extract one element, not the array of numerical values. – xaviersjs Jul 11 '22 at 17:10
  • 1
    @xaviersjs i believe the OP wanted just one element. – MattKing Jul 11 '22 at 18:01
0

I've tested your function and the error Function MULTIPLY parameter 2 expects number values. But 'Date' is a text and cannot be coerced to a number is due to this part E2*GOOGLEFINANCE("Currency:"&F2&$G$1,"price", H2) in your IFS function.

The return value of the GOOGLEFINANCE("Currency:"&F2&$G$1,"price", H2) is an array (it contains strings and numbers) and multiplying it to E2 value is not possible. Also, if running GOOGLEFINANCE("Currency:"&F2&$G$1,"price", H2) alone works just fine, then the H2 (the date cell) isn't the main cause of the error.

RECOMMENDED SOLUTION:

I've checked on how to only return price instead of an array on GOOGLEFINANCE function and stumbled upon an answer from How can I get GOOGLEFINANCE to return only the historical price of a stock and not an array?.

Instead, you can try this function below:

=IFS(F2<>"EUR", min(GoogleFinance("Currency:"&F2&$G$1, "PRICE", H2))*E2, F2 = "EUR",E2)

Here's a sample test on my end:

enter image description here

SputnikDrunk2
  • 3,398
  • 1
  • 5
  • 17
  • Thanks for the help! Unfortunately when I do this I now get this error message: When evaluating GOOGLEFINANCE, the query for the symbol: 'CURRENCY:AUDEUR' returned no data. This doesn't work either: GOOGLEFINANCE("Currency:"&F2&$G$1,"price", H2) I get the error message that date is a text - I am getting the values from another sheet with index match not sure if that complicates the matter – Chiara Apr 27 '21 at 06:25
  • You're welcome. It would help if you can share a snippet of your data/sample sheet so we can replicate your issue properly. – SputnikDrunk2 Apr 27 '21 at 14:58