TL;DR: There are two functions below. They perform the same task, but the top one is supposed to accept a starting point for a range and a count instead of the bottom one which needs a fixed range. The problem is that the top function does not work unless data is placed in the same sheet. This is annoying, and more so since i don't understand why.
I was relieved when I found that someone had made a decent interpolator for excel, because they can only trend between 2 points and that is just not good enough to fit my measurements.
I decided to modify the formula so it does not use rigid "range" objects but are given a start "cell" (range) and then a count integer of the dimensions of the range.
My data now has to be on the same sheet as the calculation, or else it will not work. Can you please take a look and see why this is?
My function (I have marked a | on the rows I have changed)
| Function LINTERPX(x As Double, rXs As Range, rYs As Range, No As Integer) As Variant
| With ThisWorkbook
Dim i As Long ' index to rY
Dim dF As Double ' interpolation fraction
Dim v As Variant ' for each/loop control variable
| Dim rX As Range
| Dim rY As Range
| Set rX = Range(Cells(rXs.Row, rXs.Column), Cells(rXs.Row + No - 1, rXs.Column))
| Set rY = Range(Cells(rYs.Row, rYs.Column), Cells(rYs.Row + No - 1, rYs.Column))
| End With
For Each v In Array(rX, rY)
If v.Areas.Count > 1 Then GoTo Oops
If v.Rows.Count <> 1 And v.Columns.Count <> 1 Then GoTo Oops
If WorksheetFunction.Count(v) <> v.Count Then GoTo Oops
Next v
If rX.Count < 2 Then GoTo Oops
If rX.Count <> rY.Count Then GoTo Oops
dFrac x, rX, i, dF, IIf(rX(2).Value2 > rX(1).Value2, 1, -1)
LINTERPX = rY(i).Value2 * (1 - dF) + rY(i + 1).Value2 * dF
Exit Function
Oops:
LINTERPX = CVErr(xlErrValue)
End Function
Original:
Function LINTERP(x As Double, rX As Range, rY As Range) As Variant
' shg 1997-0606, 2009-0419
' 2009-0604 added option for descending sort
' Linear interpolator / extrapolator
' Interpolates rX to return the value of y corresponding to the given x
' rX and rY must be equal-length vectors
' rX must be sorted (ascending or descending, doesn't matter)
Dim i As Long ' index to rY
Dim dF As Double ' interpolation fraction
Dim v As Variant ' for each/loop control variable
For Each v In Array(rX, rY)
If v.Areas.Count > 1 Then GoTo Oops
If v.Rows.Count <> 1 And v.Columns.Count <> 1 Then GoTo Oops
If WorksheetFunction.Count(v) <> v.Count Then GoTo Oops
Next v
If rX.Count < 2 Then GoTo Oops
If rX.Count <> rY.Count Then GoTo Oops
dFrac x, rX, i, dF, IIf(rX(2).Value2 > rX(1).Value2, 1, -1)
LINTERP = rY(i).Value2 * (1 - dF) + rY(i + 1).Value2 * dF
Exit Function
Oops:
LINTERP = CVErr(xlErrValue)
End Function
