CODEWITHSUNDEEP
excelvba
Felipe
Felipe

Reputation: 163

Worksheetfunction.average returning error when range contains empty cells or cells with text

I am dealing with small ranges (only one column) that can contain both numbers and text (or empty cells). The description of the worksheetfunction.average says that "Arguments can either be numbers or names, arrays, or references that contain numbers." and "If an array or reference argument contains text, logical values, or empty cells, those values are ignored." It says too that "Empty cells are not counted, but zero values are."

However, 

With Sh 
SlpStdBlkCorr_Sh.Range("T2") = WorksheetFunction.Average(.Range(RawPb208Range)) 
End with

raises the "unable to get the average property of the worksheetfunction class" error.

This line of code is used repeatedly, so all variables are perfectly set and this error only raises when the range has empty cells or cells with text.

Shouldn't the .average ignore these cells with non-numeric contents?

I have tried the solution by Paul Aford, but the problem is that as I am calculating somethings and I can create some monsters just equaling variables to 0 (SlpStdBlkCorr_Sh.Range("T2")=0) or 1 (SlpStdBlkCorr_Sh.Range("T2")=1).

Ideas?

Upvotes: 1

Views: 2982

Answers (2)

GTaylor
GTaylor

Reputation: 121

I have the same issue when all the cells are empty. If at least one of them isn't blank, it works as advertised.

Starting at column AA in groups of 5, where arr is an array and 'row' is a passed-in argument.

    c = 27 ' column AA
    For i = 0 To 9 ' for 10 groups
        If WorksheetFunction.CountBlank(Range(Cells(row, c), Cells(row, c + 4))) < 5 Then
            arr(i) = WorksheetFunction.Average(Range(Cells(row, c), Cells(row, c + 4)))
            c = c + 5
        End If
    Next i

Upvotes: 0

Doug Glancy
Doug Glancy

Reputation: 27488

In my testing it errors only when the range contains only non-numeric values and/or blank cells. This matches the behavior of the AVERAGE function in a worksheet. If you use have a formula with, for example, =AVERAGE(A1:A10) and those cells are all non-numeric or blank, it will return "#DIV/0".

This makes sense since with no numerics there's nothing to divide by, so the denominator is zero.

Here's a worksheet selection change event you can use to test this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Count function returns cells with numeric values    
If WorksheetFunction.Count(Selection) > 0 Then
    Debug.Print WorksheetFunction.Average(Selection)
Else
    Debug.Print "Nothing to Average"
End If
End Sub

Note that you'll see the same error message if one or more cells in the range has a formula that returns an error, such as #N/A.

Upvotes: 1

Related Questions