UDF with conditions

[TG2812]

Hi, I built the following UDF and I need to do the following adjustments;

#adjustment 1: When my function returns a result greater to 7, the formula needs to return ">7" in the worksheet. I could handle this with a basic IF in the worksheet but I would need to go through my 100000+ rows...Is this possible to adjust the UDF without using IF statement either in the worksheet nor in the UDF?

#adjustment 2: When my array ends, the last result the formula returns in the worksheet is #VALUE . How can I handle this and by default returning 0 ?

Thank you in advance for all your answers !

Function MyFunction(Par1 As Double, myArray)
 
myFunction = 0
Dim i As Variant
 
For Each i In myArray
    If Par1 = 0 Then Exit Function
    If Par1 < i Then
        MyFunction = MyFunction + Par1 / i
        Exit Function
    Else
        Par1 = Par1 - i
        MyFunction = MyFunction + 1
    End If
Next i
 
End Function

 

[rlv01]

Perhaps something like this (not tested)

Function MyFunction(Par1 As Double, myArray As Variant) As Variant
    Dim MyScratchVar As Double
    Dim ResultFound As Boolean

    ResultFound = False
    MyScratchVar = 0
    Dim i As Variant

    For Each i In myArray
        If Par1 = 0 Then Exit For
        If Par1 < i Then
            MyScratchVar = MyScratchVar + Par1 / i
            ResultFound = True
            Exit For
        Else
            Par1 = Par1 - i
            MyScratchVar = MyScratchVar + 1
        End If
    Next i

    If ResultFound Then
        If MyScratchVar > 7 Then
            MyFunction = ">7"
        Else
            MyFunction = MyScratchVar
        End If
    Else
        MyFunction = 0
    End If
End Function

 

[TG2812]

It unfortunately does not work.

For #2 , the error #VALUE is returned when Par 1 is negative and the first element of the array is 0.

Any other idea?

Thank you in advance for your help.

 

[rlv01]

It unfortunately does not work.
the error #VALUE is returned when Par 1 is negative and the first element of the array is 0.

The condition (Par1 < i AND i = 0) will always produce a divide-by-zero error. You have to decide what you want to happen when that condition occurs.

 

[TG2812]

In such case, I would like the function to return 0 but I'm not able to make it happen in the code. Any idea?

 

[rlv01]

Function MyFunction(ByVal Par1 As Double, myArray As Variant) As Variant
    Dim MyScratchVar As Double
    Dim ResultFound As Boolean

    ResultFound = False
    MyScratchVar = 0
    Dim i As Variant

    For Each i In myArray
        If Par1 < i And i = 0 Then Exit For
        If Par1 = 0 Then Exit For
        If Par1 < i Then
            MyScratchVar = MyScratchVar + Par1 / i
            ResultFound = True
            Exit For
        Else
            Par1 = Par1 - i
            MyScratchVar = MyScratchVar + 1
        End If
    Next i

    If ResultFound Then
        If MyScratchVar > 7 Then
            MyFunction = ">7"
        Else
            MyFunction = MyScratchVar
        End If
    Else
        MyFunction = 0
    End If
End Function

 

[TG2812]

Thank you rlv01. I came up with a table to illustrate what I discussed above.

[TABLE="width: 826"]
<colgroup><col><col><col span="3"><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]Jan-19[/TD]
[TD="align: right"]Feb-19[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD="align: right"]Jul-19[/TD]
[TD="align: right"]Aug-19[/TD]
[TD="align: right"]Sep-19[/TD]
[/TR]
[TR]
[TD]My sales forecast[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]My inventory projection[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]My invenrory cover [/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]0.33[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]8.00[/TD]
[/TR]
</tbody>[/TABLE]

"My Inventory cover" corresponds to the row where I input the UDF. Do you obtain the same result with the code pasted above?

Thank you in advance.

 

[rlv01]

The function depends on two inputs Par1 and myArray. You are assuming that I will know how to figure out those inputs from the sales/proj/cover table you posted above. Better not to make assumptions like that, but instead explain how the data relates to the function.

 

[TG2812]

Let me further explain. What I'm trying to do is to understand how long it will take for my inventory (projection) to deplete based on my sales forecast.
Par1 here corresponds to -3, myArray corresponds to the sales forecast from Feb-19 to Sep-19 (0,0,0,3,0,3,1,3).

Based on these inputs, I'm obtaining the figures shown in the row "My Inventory Cover".

 

[rlv01]

For your results row

My invenrory cover #VALUE ! #VALUE ! #VALUE ! 0.00 2.00 0.33 8.00 8.00 8.00​

Can you tell me what the results should be for each column, if you were doing a hand calculation?