UDF para interpolación y extrapolación lineal

Código

Function IB·InterExtrapolaciónLineal(Rango_X As Range, Rango_Y As Range, X As Double) As Variant

Dim i As Long, n As Long, x1 As Double, x2 As Double, y1 As Double, y2 As Double

n = Rango_X.Rows.Count

For i = 1 To n - 1

If X >= Rango_X.Cells(i, 1).Value And X <= Rango_X.Cells(i + 1, 1).Value Then

x1 = Rango_X.Cells(i, 1).Value

x2 = Rango_X.Cells(i + 1, 1).Value

y1 = Rango_Y.Cells(i, 1).Value

y2 = Rango_Y.Cells(i + 1, 1).Value

IB·InterExtrapolaciónLineal = y1 + (y2 - y1) * (X - x1) / (x2 - x1)

Exit Function

End If

Next i

If X < Rango_X.Cells(1, 1).Value Then

x1 = Rango_X.Cells(1, 1).Value

x2 = Rango_X.Cells(2, 1).Value

y1 = Rango_Y.Cells(1, 1).Value

y2 = Rango_Y.Cells(2, 1).Value

IB·InterExtrapolaciónLineal = y1 + (y2 - y1) * (X - x1) / (x2 - x1)

ElseIf X > Rango_X.Cells(n, 1).Value Then

x1 = Rango_X.Cells(n - 1, 1).Value

x2 = Rango_X.Cells(n, 1).Value

y1 = Rango_Y.Cells(n - 1, 1).Value

y2 = Rango_Y.Cells(n, 1).Value

IB·InterExtrapolaciónLineal = y1 + (y2 - y1) * (X - x1) / (x2 - x1)

Else

IB·InterExtrapolaciónLineal = "Valor fuera del rango."

End If

End Function