Calculate the average of a column with time excluding rows with values in other columns

Question

I'm trying to calculate the average in column (T) with time on it (00:00:00), and exclude the rows with blank cells on column "E" and the text "PC" on column "R".

This is what I've tried so far:

    Dim ws As Worksheet
    Set ws = Worksheets("mapa_cargas")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.count, "T").End(xlUp).Row 
    
    Dim totalHours As Double
    totalHours = 0
    
    Dim count2 As Long
    count2 = 0
    
    For i = 1 To lastRow
        ' Check if the current row contains a value to exclude
        If ws.Cells(i, 5) = "" And ws.Cells(i, 18) = "" And ws.Cells(i, 20) = "" Then 
            totalHours = totalHours + ws.Cells(i, 20).Value * 24 
            count2 = count2 + 1
        End If
    Next i
    
    Dim averageHours As Double
    averageHours = totalHours / count2
    

    
    Worksheets("indicadores").Cells(4, 7).Value = averageHours

It returns the run time error 6, overflow on averageHours = totalHours / count2

In this specific case, i need to average only the yellow cells.

Answer 1

Please, try the next way. It should be very fast even for big ranges, using arrays and working only in memory:

Sub timeAverage()
    Dim ws As Worksheet
    Set ws = ActiveSheet 'Worksheets("mapa_cargas")
    
    Dim lastRow As Long
    lastRow = ws.cells(ws.rows.count, "T").End(xlUp).row
    
    Dim arr, arrAV, i As Long, k As Long
    arr = ws.Range("E1:T" & lastRow).Value2 'place the range in an array for faster processing
    ReDim arrAV(UBound(arr) - 1) 'redim the average array of a maximum
    
    For i = 1 To UBound(arr)
        ' Check if the current array row contains a value to exclude
        If arr(i, 1) <> "" And arr(i, 14) <> "PC" And arr(i, 16) <> "" Then
            arrAV(k) = arr(i, 16): k = k + 1 'place the matching values in the array
        End If
    Next i
    
    If k > 0 Then
        ReDim Preserve arrAV(k - 1) 'eliminate the empty array elements
        Worksheets("indicadores").cells(4, 7).Value = Format(WorksheetFunction.Average(arrAV), "hh:mm:ss")
    Else
        MsgBox "No values existing to make average...": Exit Sub
    End If

    MsgBox "Ready..."
End Sub

Your code could not work, even if it should be slow, since it only summarize the values if all the checked columns are empty. And try making average from empty values...