tl;dr how can I make the script take an entire column (eg A:A) and then ignore the first (header) row, and any blank rows?
Background
I have an array in Google Sheets with date-ordered data (note dates are in UK format), where I will routinely add new entries every few weeks.
A B
1 Recorded date Value
2 17/3/2018 120
3 1/9/2018 145
4 31/12/2018 210
5 31/3/2019 273
6 5/6/2019 412
In a separate sheet, I want to be able to list other dates, lookup against those dates, and return either the right value (if it matches one of the recorded dates) OR a linear interpolation between the two closest dates.
For instance:
A B C D E
1 Recorded date Value Lookup date Result
2 17/3/2018 120 1/9/2018 145
3 1/9/2018 145 14/2/2019 241.51
4 31/12/2018 210
5 31/3/2019 273
6 5/6/2019 412
(In reality, the lookup cells are daily rows in a different sheet)
I have achieved the simple mechanics of interpolation by adapting the great answers to the question How to interpolate data in a range in Google Sheets. Indeed, I have put my current code below for reference.
PROBLEM: I want to feed the entire column into the script, not just the rows 2-6
My current problem is that this script only works if the inputted x and y ranges cover the exact range of available data. Therefore if more observations are recorded into columns A and B, I have to change the formula.
So in the example above, I need to use the following formula in cell E2 otherwise it returns an error:
=getvalue(A2:A6,B2:B6,D2)
Ideally, the formula in E2 would be the following, which would allow the E column to always work regardless of the number of observations recorded in A and B:
=getvalue(A:A,B:B,D2)
In short: how can I make the script take an entire column (eg A:A) and then ignore the first (header) row, and any blank rows?
Here is my current script:
function getvalue(x, y, lookupdate) {
if (lookupdate.map) {
return lookupdate.map(function(v) {
return getvalue(x, y, v);
});
}
if (lookupdate > Math.max.apply(Math, x) || lookupdate < Math.min.apply(Math, x)) {
throw "lookupdate can't be interpolated !!";
return;
}
var check = 0, index;
for(var i = 0, iLen = x.length; i < iLen; i++) {
if(x[i][0] == lookupdate) {
return y[i][0];
} else {
if(x[i][0] < lookupdate && ((x[i][0] - check) < (lookupdate - check))) {
check = x[i][0];
index = i;
}
}
}
var xValue, yValue, xDiff, yDiff, xInt;
yValue = y[index][0];
xDiff = x[index+1][0] - check;
yDiff = y[index+1][0] - yValue;
xInt = lookupdate - check;
return (xInt * (yDiff / xDiff)) + yValue;
}
When I run this script with the x/y inputs as A:A and B:B I get the error "TypeError: Cannot read property "0" from undefined. (line 27)."
And line 27 of my script is:
xDiff = x[index+1][0] - check;
Also, this is my first question – so let me know if I should format/ask it differently!
getvalueTEST
, it callsgetvalue
but this function isn't included.