Created
October 21, 2021 15:38
-
-
Save pyRobShrk/0710c79d1e617c68a4d00c8cf8e547fd to your computer and use it in GitHub Desktop.
Linear Interpolation function for Excel VBA
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Function interp(X As Double, xRange As Range, yRange As Range) As Double | |
| ascending = xRange.Cells(1) < xRange.Cells(2) | |
| With WorksheetFunction | |
| If ascending Then i = .Match(X, xRange) Else i = .Match(X, xRange, -1) | |
| Set x1x2 = Range(xRange.Cells(i), xRange.Cells(i + 1)) | |
| Set y1y2 = Range(yRange.Cells(i), yRange.Cells(i + 1)) | |
| interp = X * .Slope(y1y2, x1x2) + .Intercept(y1y2, x1x2) | |
| End With | |
| End Function |
A simplified version of this function (supporting only ascending columns), can be entered in the Name Manager in new versions of 365. Create a new name called interp, with this as the formula:
=LAMBDA(xval,x,y,LET(i,MATCH(xval,x),mb,LINEST(OFFSET(y,i-1,0,2),OFFSET(x,i-1,0,2)),INDEX(mb,1)*xval+INDEX(mb,2)))
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
There are many like it but this one is mine. All the work is handled by built-in Excel functions (Match, Slope, Intercept). It supports inputs as rows or columns, ascending or descending.