I have a custom function which calculates linearly interpolated values with a specific base value, accounting for that base value and reducing the total to be the same as if the base value was 0. I calculate its t value inside the argument parentheses, and this seems to be the issue.

I want to make it work within array formulae, but the function refuses to work if its t value is calculated within the argument parentheses. It works if I pre-calculate the t values in a separate column and just reference that range, but I’d like to avoid having to do that for the amount of different places I need to use this function in.

Here’s the function in question, it is adapted from this solution:

Function BasedLerp(a As Single, b As Single, t As Variant) As Variant
    Dim r() As Single
    Dim i As Integer

    'THIS FUNCTION APPROXIMATES THE SAME SUM OF VALUES AS IF IT WAS A 'Lerp(0,b,t)' FUNCTION, BY ACCOUNTING FOR 'a' BEING NON-ZERO
    
    aVal = (a - (b / 2)) * 2 'The Function works on the [-1 ,1] range, this adjust a [0,1] input
    x = (aVal - b) / 2
    
    If Not (isArray(t)) Then
        BasedLerp = (aVal + ((b - aVal) * t)) - x
    ElseIf TypeOf t Is Excel.Range Then
        ReDim r(1 To t.Cells.Count)
        For i = 1 To t.Cells.Count
            r(i) = (aVal + ((b - aVal) * t.Cells(i).Value)) - x
        Next i

        If t.Rows.Count = 1 Then
            BasedLerp = r
        Else
            BasedLerp = Application.Transpose(r)
        End If
    Else
        ReDim r(LBound(t) To UBound(t))
        For i = LBound(t) To UBound(t)
            r(i) = (aVal + ((b - aVal) * t(i))) - x
        Next i

        BasedLerp = r
    End If

End Function

The way I want to use it is

=BasedLerp([base value];1;(H14:H36/I14:I36))

And it throws #Value errors

If I changed it to

=BasedLerp([base value];1;(H14:H36))

It would work, but that requires me pre-calculating the t values in the range in question, it would add dozens of unnecessary columns in my sheet.

Is this possible? My current assumption is that the return value of (H14:H36/I14:I36) is not accounted for in the script, but I can’t find any documentation on what the return type and format of it would be.