I've got a spreadsheet for tracking share prices and then as they're in dollars, converting back to GBP currency.
I'm noticing some very strange behaviour with the currency conversion part.
Example spreadsheet here:
https://docs.google.com/spreadsheets/d/13V9SIwOWSARLrP2jLdbbzCasHYxDglLnXozlwRyh3Zw/edit?usp=sharing
Here's how it works:
Column A, is the date from another sheet converted to integer
Column E, is the google finance function which creates a table for date and closing price
Column D, is the date from column E converted to integer
I have to convert both dates to integer form as the share prices don't get updated until 4pm, whereas currency gets updated at 11:58pm.
To also add confusion, share prices only have updates M-F, whereas currency runs all week.
In column C, i use a lookup function to search for the "date-integer" in column A, with the "date-integer" in column D and then pull the value from the closing price in column F. (I've coloured a few of the cells to hopefully make it look obvious what is going on).
Now the lookup function all works correctly as expected, but where it gets strange is when trying to expand the search range past row 83.
Example:
Formula in cell C5 [=lookup(A5,$D$5:$F$83)] which returns the value in F5. If i change the search range to $D$5:$F$84, it returns a blank value, not even a #N/A or #REF, or #whatever.
I could understand if it failed past row 44, as row 45 will only get updated tomorrow when the closing currency value.
I've tried vlookup/hlookup functions as well to see if that makes any difference.
Is anyone able to see what might be going wrong here?
Cheers in advance!
I'm noticing some very strange behaviour with the currency conversion part.
Example spreadsheet here:
https://docs.google.com/spreadsheets/d/13V9SIwOWSARLrP2jLdbbzCasHYxDglLnXozlwRyh3Zw/edit?usp=sharing
Here's how it works:
Column A, is the date from another sheet converted to integer
Column E, is the google finance function which creates a table for date and closing price
Column D, is the date from column E converted to integer
I have to convert both dates to integer form as the share prices don't get updated until 4pm, whereas currency gets updated at 11:58pm.
To also add confusion, share prices only have updates M-F, whereas currency runs all week.
In column C, i use a lookup function to search for the "date-integer" in column A, with the "date-integer" in column D and then pull the value from the closing price in column F. (I've coloured a few of the cells to hopefully make it look obvious what is going on).
Now the lookup function all works correctly as expected, but where it gets strange is when trying to expand the search range past row 83.
Example:
Formula in cell C5 [=lookup(A5,$D$5:$F$83)] which returns the value in F5. If i change the search range to $D$5:$F$84, it returns a blank value, not even a #N/A or #REF, or #whatever.
I could understand if it failed past row 44, as row 45 will only get updated tomorrow when the closing currency value.
I've tried vlookup/hlookup functions as well to see if that makes any difference.
Is anyone able to see what might be going wrong here?
Cheers in advance!