Public Function QuantilFinder(q As Double) As Variant
With Worksheets("Quantile")
With .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
If .Row < 2 Then
QuantilFinder = Empty
Exit Function
End If
i = 1
j = .Count
Do While i < j
m = (i + j) \ 2
If q > .Cells(m, 1).Value Then
i = m + 1
ElseIf q < .Cells(m, 1).Value Then
j = m - 1
Else
QuantilFinder = .Cells(m, 1).Offset(0, 1).Value
Exit Function
End If
Loop
If VarType(.Cells(i, 1).Offset(-1, 0).Value) = 8 Then
QuantilFinder = .Cells(1, 1).Offset(0, 1).Value
Exit Function
ElseIf VarType(.Cells(i, 1).Offset(1, 0).Value) = 0 Then
l1 = Abs(q - .Cells(i, 1).Offset(-1, 0).Value)
l2 = Abs(q - .Cells(i, 1).Value)
If l1 < l2 Then
QuantilFinder = .Cells(i, 1).Offset(-1, 1).Value
Exit Function
Else
QuantilFinder = .Cells(i, 1).Offset(0, 1).Value
End If
Exit Function
End If
l1 = Abs(q - .Cells(i, 1).Offset(-1, 0).Value)
l2 = Abs(q - .Cells(i, 1).Value)
l3 = Abs(q - .Cells(i, 1).Offset(1, 0).Value)
If l1 < l2 Then
QuantilFinder = .Cells(i, 1).Offset(-1, 1).Value
ElseIf l2 <= l1 And l2 < l3 Then
QuantilFinder = .Cells(i, 1).Offset(0, 1).Value
ElseIf l3 <= l2 Then
QuantilFinder = .Cells(i, 1).Offset(1, 1).Value
End If
End With
End With
End Function