Something wrong with the IF statement

**billj** · 11-04-2016, 06:21 AM

Hi everyone,

I have a simple macro below for my purpose. This function always returns 0 if there are error values in the range (e.g. #N/A, #DIV/0!). I feel there is something wrong with the IF statement?

Function CustomAVG(rData As Range, dPct1 As Double, dPct2 As Double, Optional dLower As Double, Optional dUpper As Double) As Double
    Dim i As Long, c As Range, a() As Variant
    i = -1
    On Error Resume Next
    For Each c In rData
        If Not IsError(c.Value + 0) And (Len(c.Value) > 0) Then
            i = i + 1
            ReDim Preserve a(i) As Variant
            a(i) = c
        End If
    Next c
    CustomAVG = WorksheetFunction.Percentile(a, dPct1)
End Function

**cytop** · 11-04-2016, 06:30 AM

I feel there is something wrong with the IF statement?

Care to explain?

**Tsjallie** · 11-04-2016, 06:49 AM

Not sure, but I think the problem is in the values you move into the array.
Try changing that to

a(i) = CDbl(c)

Then the function gives a value other than 0.
Not sure if that's the value you're looking for.

**billj** · 11-04-2016, 07:00 AM

HI Tsjallie, it does work! May I ask why do I need to force the conversion? I thought array a() is defined as type variant which can accept any values.

**Tsjallie** · 11-04-2016, 07:06 AM

As you say the array is of type variant, so will accept any kind of data.
Problem is with the percentile funtion which needs numeric data. The array gets fed with things like "Error 2007" or alike.
With cdbl (or may be that should be clng) the errorcode is isolated and so gets fed with numeric data.

**billj** · 11-04-2016, 07:12 AM

Hi Tsjallie, can you look at the code below? There is no use of Cdbl but still worked

Thank you for helping.

Function fPercentile(rData As Range, dPercentile As Double) As Double
    Dim i As Long, c As Range, a() As Variant
    i = -1
    For Each c In rData
      If Not IsError(c) Then
        i = i + 1
        ReDim Preserve a(i) As Variant
        a(i) = c
      End If
    Next c
    fPercentile = WorksheetFunction.Percentile(a, dPercentile)
End Function

**Tsjallie** · 11-04-2016, 07:48 AM

Took a closer look at the macro and found that I need glasses. Thought you wanted to assemble error codes in the array (and wndeered why), but you're not. Overlooked the NOT if the if-statement.
Problem is in the len-function.
If a cell with an error is evaluated then the len-function gives a type mismatch.
But, since you have set On Error Resume Next the function happily proceeds with the next line.
So these non-numeric data get into the array.

What you need to do is split up the if-statement into two parts. And I also think you can leave out the On Error statement.

Function CustomAVG(rData As Range, dPct1 As Double, dPct2 As Double, Optional dLower As Double, Optional dUpper As Double) As Double
    Dim i As Long, c As Range, a() As Variant
    i = -1
    'On Error Resume Next
    For Each c In rData
        If Not IsError(c.Value) Then
            If Len(c.Value) > 0 Then 'The len function is now only executed for cells not having an error
                i = i + 1
                ReDim Preserve a(i) As Variant
                a(i) = c
            End If
        End If
    Next c
    CustomAVG = WorksheetFunction.Percentile(a, dPct1)
End Function

This also explains why the other function did work.

**billj** · 11-04-2016, 09:18 AM

Hi Tsjallie, thank you so much for the great explanation. The new code you provided now works perfectly. Very good knowledge to me. Thank you.

May I ask the final question: if I change the statement:

If Not IsError(c.Value) Then

to

If Not IsError(c.Value + 0) Then

The function resulted in #VALUE!. If I remove the part + 0, then the function works. Do you know why?

**billj** · 11-04-2016, 09:49 AM

The following code will result in #VALUE! error. But it will work if the c.Value + 0 becomes c.Value. Not sure why? The sample file is attached.

Function CustomPct(rData As Range, dPct As Double) As Double
    Dim i As Long, c As Range, a() As Variant
    i = -1
    For Each c In rData
        If Not IsError(c.Value + 0) Then
            If Len(c.Value) > 0 Then
                i = i + 1
                ReDim Preserve a(i) As Variant
                a(i) = c
            End If
        End If
    Next c
    CustomPct = WorksheetFunction.Percentile(a, dPct)
End Function