Months of Coverage

[spcalan]

Today I use this function to calculate months of coverage
=coverage(Cell that holds inventory, range of cells that have the monthly forecast)
=coverage(F7,G5:I5)

I want to use a function that does the same thing, but with value in the function such as :

=coverage(1000,500,250,150,50)

Thoughts ?

Function coverage(inventory, forecast)
Application.Volatile
For Each c In forecast
inventory = inventory - c.Value
Select Case inventory
Case Is > 0
coverage = coverage + 1
Case Is = 0
coverage = coverage + 1
Exit Function
Case Is < 0
If c.Value <> 0 Then
coverage = coverage + (1 - (Abs(inventory) / c.Value))
Else
coverage = coverage
End If
Exit Function
End Select
Next c
End Function

 

[Scott Huish]

Try this:

Function coverage(inventory, ParamArray forecast() As Variant)
Application.Volatile
Dim c As Long
For c = LBound(forecast) To UBound(forecast)
    inventory = inventory - forecast(c)
    Select Case inventory
    Case Is > 0
        coverage = coverage + 1
    Case Is = 0
        coverage = coverage + 1
        Exit Function
    Case Is < 0
        If c <> 0 Then
        coverage = coverage + (1 - (Abs(inventory) / forecast(c)))
    Else
        coverage = coverage
    End If
        Exit Function
    End Select
Next c
End Function

 

[Rijnsent]

Hi Spcalan,
so your function would have 1 input for "inventory" and after that multiple for the monthly forecast? Is there some kind of logic to the number of cells you'd want to use for the forecast? And are they single cells or do you want multiple ranges (with each multiple cells)?
Cheers,
Koen

 

[spcalan]

Try this:

Function coverage(inventory, ParamArray forecast() As Variant)
Application.Volatile
Dim c As Long
For c = LBound(forecast) To UBound(forecast)
    inventory = inventory - forecast(c)
    Select Case inventory
    Case Is > 0
        coverage = coverage + 1
    Case Is = 0
        coverage = coverage + 1
        Exit Function
    Case Is < 0
        If c <> 0 Then
        coverage = coverage + (1 - (Abs(inventory) / forecast(c)))
    Else
        coverage = coverage
    End If
        Exit Function
    End Select
Next c
End Function

Thanks !
works perfect .