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

My invenrory cover

#VALUE!

#VALUE!

#VALUE!

0.00

2.00

0.33

8.00

8.00

8.00

<colgroup><col><col><col span="3"><col span="5"></colgroup><tbody>
</tbody>

the results are

My invenrory cover

#VALUE!

#VALUE!

#VALUE!

0.00

5

4.333333333

7

6.33333333

5

<colgroup><col><col><col span="3"><col span="5"></colgroup><tbody>
</tbody>

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.