2

I want to write an INTERPOLATION function in JavaScript in order to be able to use Google Sheets instead of Excel for a number of purposes. In Excel, I have this user defined function written in VBA:

Function interpolate_1D(xreq As Single, x As Range, y As Range) As Single

' If we have variable y that is a function of x and have two ranges, x and y that give the values
'of y for particular values of x, we may need to find the value of y for a value of x not 'given in the table.
'For example, we may have power curve data for a wind turbine that gives the power output of a wind turbine
'for integer values of the wind speed. To find the output power at any other speed we         could 'use this function, using as arguments:
'xreq: wind speed for which we wish to know the output power
'x: range containing the known wind speeds in ascending order
'y: range containing the known wind turbine powers

Dim pointer As Integer
Dim x0 As Single, y0 As Single, x1 As Single, y1 As Single

pointer = Application.Match(xreq, x, 1)
x0 = x(pointer)
x1 = x(pointer + 1)
y0 = y(pointer)
y1 = y(pointer + 1)

interpolate_1D = y0 + (xreq - x0) * (y1 - y0) / (x1 - x0)

End Function

I probably copied this from somewhere, such as Billo's book on Excel for Scientists and Engineers. It works very well, as does a 2D version of it that I wrote.

I am fairly new to JavaScript, and I can't at the moment see how to get it to do the equivalent of the Application.Match (xreq,x,1) line, where it looks through the range of known x values and finds the position of the largest value that is smaller than the search value xreq. Once i have that position, I can do everything else.

Any ideas?

mbh038
  • 23
  • 2

3 Answers3

1

You could probably use the function given here and add your own logic in the loop to find the value you are looking for.

Community
  • 1
  • 1
D.M.
  • 185
  • 4
1

You may not need to. You may be able to use the array .IndexOf method:

http://www.w3schools.com/jsref/jsref_indexof_array.asp

var fruits = ["Banana", "Orange", "Apple", "Mango"];
var a = fruits.indexOf("Apple");

The result of a will be: 2

Whether it is possible to recreate the optimized Match worksheet function in another language would require some more detailed knowledge of the underlying code, which I can't offer you at this time.

Otherwise, without seeing more of what you're doing, I'd say trying to reconstruct the Match function -- although it will be slower -- is as simple as brute force iteration over the array (this will be faster if you test on an array, rather than the range object).

I have modified Tim Williams' test functions to compare several methods. In these tests, the Contains function is tested against the range .Value after transferring that to an array. I test Match function both against the array, and against the range object, and also test WorksheetFunction.Match against the range.

Sub Tester()

    Application.ScreenUpdating = False
    Dim i As Long, B, T
    Dim Arr As Variant
    Dim rng As Range
    Set rng = Range("A1:A10000")

    rng.Formula = "=""value_""&" & "RandBetween(1,1000)"
    Range("A100000").Value = "Value_50"

    T = Timer
    Arr = Application.Transpose(rng.Value)
    For i = 1 To 10000
        B = Contains(Arr, "Value_50")
    Next i
    Debug.Print "Contains (array)" & vbTab & Timer - T

    T = Timer
    Arr = Application.Transpose(rng.Value)
    For i = 1 To 10000
        B = Application.Match("Value_50", Arr, False)
    Next i
    Debug.Print "Match (array)" & vbTab & Timer - T

    T = Timer
    For i = 1 To 10000
        B = Application.Match("Value_50", rng, False)
    Next i
    Debug.Print "Match (range)" & vbTab & Timer - T

    T = Timer
    On Error Resume Next
    For i = 1 To 10000
        B = Application.WorksheetFunction.Match("Value_50", rng, False)
    Next i
    On Error GoTo 0
    Debug.Print "WorksheetFunction.Match (range)" & vbTab & Timer - T


    Application.ScreenUpdating = True
End Sub

Observed that the Contains custom function is faster than Match when performed on an array in memory, however the Application.Match and Application.WorksheetFunction.Match functions are both remarkably faster than either, when performed on a Range object on the worksheet:

Contains (array)    18.90625
Match (array)   43.25
Match (range)   0.2304688
WorksheetFunction.Match (range) 0.1914063

Thanks to the comments/discussion with @CharlesWilliams for assistance with this timing test & clarifying some things about it.

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Tim Williams method is faster than using .MATCH if the array already exists as a VBA array because each call to .MATCH incurs the large overhead of transferring the array to something that .MATCH can process. If (as in the question above) the data is an Excel range it is much faster to use .MATCH (avoids the data transformation overhead) – Charles Williams Aug 23 '14 at 14:48
  • @CharlesWilliams That appears to be false based on some tests that I have just done -- it is faster to transfer the range `.value` to an array, and work on the data in memory. This is nearly always true, the overhead is minimal in that case, and working on the array in memory is faster than accessing the range object or worksheet object. – David Zemens Aug 23 '14 at 15:44
  • Thanks all. I used an iterative approach before, on a range, and found MATCH to be much faster, although it doesn't matter for most of the very small data sets I normally use, and I didn't do careful tests like Tim Williams. the .IndexOf Method looks promising. I will play and let you know how far I get. Thanks again. – mbh038 Aug 23 '14 at 16:22
  • @David, speed of depends on the size of the data range and the excel version used and the search method used (binary search or linear search). For small amounts of data I have not measured the time difference because it does not matter. – Charles Williams Aug 23 '14 at 22:14
  • @CharlesWilliams There may well be circumstances where Match outperforms iteration but for now all I am contesting is the general presumption that "if the data is an Excel range it is much faster to use Match" which appears at a minimum **not** universally true. – David Zemens Aug 24 '14 at 01:18
  • @David, I suggest you rerun your tests using the correct syntax for MATCH and including Value_50 at the end of the range to be searched and using WorksheetFunction.MATCH. My results of your test modified as suggested are (using 32-bit XL 2010) Contains (array) 1.390625 Match (array) 2.976563 Match (range) 0.65625. And of course using the binary search option for MATCH (as in the OP's question) would be even faster – Charles Williams Aug 24 '14 at 22:48
  • @CharlesWilliams thanks for pointing out syntax errors. I'm square with you now: Observed that the `Contains` custom function is faster than `Match` when performed on an array in memory, however the `Application.Match` and `Application.WorksheetFunction.Match` functions are both remarkably faster than either, when performed on a `Range` object on the worksheet. – David Zemens Aug 25 '14 at 14:04
  • @David: I did a more comprehensive test to find the breakeven point between Match and Array loop. See http://fastexcel.wordpress.com/2014/08/25/vba-searching-shootout-between-worksheetfunction-match-performance-pros-and-cons/ – Charles Williams Aug 26 '14 at 07:53
  • @All,I hove got this interpolation routine to work:unction interpolate (xreq,knowndata){ var data = knowndata; for (var i = 0; i < data.length-1; i++) { if (xreq >=data[i][0] & xreq – mbh038 Aug 26 '14 at 12:49
0

@All,

In the end, I got this to work:

function interpolate (xreq,knowndata){
  var data = knowndata;
  for (var i = 0; i < data.length-1; i++) {
     if (xreq >=data[i][0] & xreq <data[i+1][0]) {break}
  }
  return data[i][1] + (data[i+1][1] - data[i][1]) / ( data[i+1][0] -  data[i][0]) * (xreq -  data[i][0]);
}

It needs error handling, but does the job that I wanted. "knowndata" is a 2 column range that contains known x and y data, with x in ascending order. "xreq" is the new x value for which we want to estimate the best value of y. This doesn't use an equivalent of MATCH, but at least it gives me a linear interpolation function that does the job, so now I can port several Excel spreadsheets into Google Sheets.

Thanks all for your input - it has been interesting to read.

mbh038
  • 23
  • 2