CODEWITHSUNDEEP
vbaexcelerror-handlingaverage
rozario_k
rozario_k

Reputation: 59

How to exclude the #VALUE! or #DIV/0! in a column for calculating average using VBA

I have a situation to calculate the average value for a column (this column have many rows with numbers and some rows with #DIV/0! and #VALUE!).

I have a macro written for calculating average function. If its full of numbers, then it easily calculates the average of the column, but if has some #VALUE! or #DIV/0! in a cell. then it returns blank cell. How can I exclude the #VALUE! and #DIV/0! error and to take average for only numbers.

I have over 5K files to calculate the average.

Private Function data As Boolean
    Dim Avg_velocity  As String
    Dim Avg_length As String
    Avg_velocity  = Application.WorksheetFunction.Average(Sheets("Data").Range("K5:K650"))
    Avg_length  = Application.WorksheetFunction.Average(Sheets("Data").Range("I7:I607"))

    Sheets("Log").Range("A2:AI2").Insert
    Sheets("Log").Cells(2, "AA").value = Avg_velocity
    Sheets("Log").Cells(2, "AB").value = Avg_length
End Function

average value affecting because of #VALUE!

Upvotes: 3

Views: 2277

Answers (3)

QHarr
QHarr

Reputation: 84465

Should also be able to use aggregate function

=Application.worksheetFunction.Aggregate(1,6,range)

Upvotes: 1

Rory
Rory

Reputation: 34035

You can also just use AverageIf to ignore errors by providing a criteria of a really large number, like this:

Avg_velocity = Application.WorksheetFunction.AverageIf(Sheets("Data").Range("K5:K650"), "<9E307")

Upvotes: 1

Vityata
Vityata

Reputation: 43565

Something like this should work quite ok:

Option Explicit

Public Sub TestMe()

    Dim myRng1  As Range
    Dim myCell  As Range
    Dim myRng2  As Range

    Set myRng1 = Range("A1:A5")

    For Each myCell In myRng1
        If Not IsError(myCell) Then
            If Not myRng2 Is Nothing Then
                Set myRng2 = Union(myRng2, myCell)
            Else
                Set myRng2 = myCell
            End If
        End If
    Next myCell

    If Not myRng2 Is Nothing Then myRng2.Select

End Sub

It goes through the range and kindly picks up only the cells, which are not errors:

enter image description here

Upvotes: 3

Related Questions