Google-sheets – INDEX-formula throws random #REF!-errors due to [illogical] column value out of range

google sheets

I have built a spreadsheet to help me keep track of my spending during my holidays. Whichever currency I buy in, it converts the raw amount into my local currency (Malaysian Ringgit MYR). Here is how it looks:

spreadsheet screenshot

I am using a combination of INDEX and GOOGLEFINANCE for this. Here is the exact code used inside cell E3:

=IF(D3="MYR",1,IF(C3="","",INDEX(GoogleFinance(CONCATENATE("CURRENCY:",D3,"MYR"),"price",B3,B3+1),2,2)))

When copying down, it seems the INDEX-function is inconsistent and throws random #REF! errors with the following error message:

Error
Function INDEX parameter 2 value is 2. Valid values are between 0 and 1 inclusive.

The thing is that there is a row 2 and column 2 for the googlefinance currency list that is referenced. I can't figure out why it is not working properly. If I reload and/or change some figures, it randomly works on a few rows. I would like a rock-solid formula that works at all times. An ideas on how to fix this?

Here is a link to a demo-sheet that is publicly editable. Play around to your hearts content! https://docs.google.com/spreadsheets/d/1SaMEfu-CpkA-m8S0V68ID9Xn5KD5Hdfk3s2vrGkhiNs/edit?usp=sharing

Best Answer

UPDATE: The #REF! errors were not returned randomly in a broad sense, they appear sometimes and once the first occurrence appear on one cell then it repeats to the cells below it.

This looks to be a latency problem inherent to GOOGLEFINANCE(), as was previously mentioned by Normal in a comment to the question.

In order to have a "rock-solid formula" instead of using GOOGLEFINANCE directly in column E use an auxiliary sheet to hold the historical currency conversion rates as fixed values instead of calculated values through a formula.

You could use GOOGLEFINANCE() to get that data but then copy and paste values only.

Short answer before the OP shared a demo spreadsheet

Instead of using ambiguous date format (00/00/0000) use a explicit one like

  • dd/mmm/yyyy
  • mmm/dd/yyyy

among others

Explanation

The problem could be due to the regional settings / date format of your spreadsheet / data.

I just tried to reproduce the problem but it works fine in my environment:

  • Date format: dd/mm/yyyy
  • Regional settings: Mexico
  • Account language: Spanish Latin America
  • Always show the function names in English

using dd/mm/yyyy as date format works fine

Reference

Related Topic