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]

Also, I cannot reproduce your results when I use your original function that you posted at the beginning of this thread.

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

When I use that function with the data set you posted, the results are not the same as what you posted in post #7 . Instead of

[TABLE="width: 826", class: grid, align: left"]
<colgroup><col><col><col span="3"><col span="5"></colgroup><tbody>[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]

the results are

[TABLE="width: 826", class: grid, align: left"]
<colgroup><col><col><col span="3"><col span="5"></colgroup><tbody>[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"]5[/TD]
[TD="align: right"]4.333333333[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6.33333333[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

What are your thoughts on why the results from your original function differ from your posted results? While the modified function I posted will handle the #adjustment 1 and # adjustment 2 which were the subject of your original post, it uses the same math used in your original function, so if the non-error results are bad for the original function, they will be bad for the new function as well.