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

A

aenemus

New Member
Joined
Mar 10, 2023
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code: 
    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
VBA Code: 
averageHours = totalHours / count2

mapa.xlsm
EFGHIJRT
1Nº transporteYARD CAR IDData RegistoHora RegistoEsperaHora EntradaCartão MotoristaEspera
2195106289829236420.03.202307:51:0101:25:0809:16:0901:25:08
3195106290029242420.03.202310:18:2500:06:1010:24:3500:06:10
4195106290229236820.03.202308:24:3100:10:2208:34:5300:10:22
5195106290329238320.03.202309:08:2700:03:4509:12:1200:03:45
6195106290529246520.03.202314:46:0402:01:4916:47:5302:01:49
7195106290729245020.03.202312:25:5100:30:5112:56:4200:30:51
8195106291029232020.03.202306:37:3600:07:5606:45:3200:07:56
9195106291129231820.03.202304:59:0100:35:2105:34:2200:35:21
10195106291229238420.03.202309:22:0800:47:4310:09:5100:47:43
11195106291329231920.03.202306:24:0100:09:2006:33:2100:09:20
12195106291929236120.03.202307:19:4400:27:3707:47:2100:27:37
13195106292229236520.03.202308:07:3200:26:3008:34:0200:26:30
14195106292429242620.03.202310:28:2000:06:5210:35:1200:06:52
15195106292529242520.03.202310:20:0300:21:0910:41:1200:21:09
16195106292629245320.03.202313:16:2000:10:1413:26:3400:10:14
17195106293129236220.03.202307:43:1000:33:0608:16:1600:33:06
18195106293329245420.03.202313:51:4000:22:0914:13:4900:22:09
19195106293429250620.03.202318:55:1600:22:1219:17:2800:22:12
20195106293529238620.03.202309:23:2700:16:4609:40:1300:16:46
21195106293629246720.03.202315:09:0800:14:4015:23:4800:14:40
22195106293729250420.03.202319:19:5400:10:2219:29:1600:10:22
23195106293829247920.03.202316:21:2000:09:4316:31:0300:09:43
24195106293929236320.03.202307:46:0600:29:3308:15:3900:29:33
25195106294029241720.03.202310:00:2200:09:1110:09:3300:09:11
26195106294429244720.03.202311:27:1400:06:1711:33:3100:06:17
27195106294629245220.03.202313:11:4601:16:1014:27:5601:16:10
28195106295229247820.03.202315:59:2800:30:4216:30:1000:30:42
29195106296229245620.03.202314:02:0700:37:5514:40:0200:37:55
30195106296629246620.03.202314:58:4100:13:1715:11:5800:13:17
31195106296829247320.03.202315:32:3401:23:0316:55:3701:23:03
32195106297429247020.03.202315:14:5400:28:0215:42:5600:28:02
33195106297929249220.03.202316:44:1700:38:3017:22:4700:38:30
34195106298429248020.03.202316:32:3800:14:4816:47:2600:14:48
35195106298529249120.03.202316:38:0800:16:0216:54:1000:16:02
36195106298829249320.03.202316:48:5300:31:1417:20:07PC00:31:14
37195106300329249420.03.202316:51:2401:01:1717:52:41PC01:01:17
38195106300829249520.03.202317:16:1100:38:0217:54:1300:38:02
39195106301029249720.03.202317:18:2600:12:5717:31:2300:12:57
40195106301229249620.03.202317:17:1700:35:4417:53:0100:35:44
41195106301529250320.03.202318:18:2300:12:1118:30:3400:12:11
42195106301729249820.03.202317:29:4900:35:0318:04:52PC00:35:03
43195106302429249920.03.202317:33:5300:39:2318:13:1600:39:23
44195106302529250220.03.202317:40:2700:13:2317:53:50PC00:13:23
45195106302929250020.03.202317:36:2900:16:5617:53:2500:16:56
46195106305429250520.03.202318:34:1300:20:4918:55:0200:20:49
47 
4829242720.03.202310:33:4000:12:1310:45:5300:12:13
49 
50 
5129242120.03.202310:14:2704:06:0114:20:28PC04:06:01
5229241820.03.202310:11:0101:04:2911:15:30PC01:04:29
5329241920.03.202310:12:4300:52:2611:05:09PC00:52:26
5429242320.03.202310:16:5500:03:2110:20:1600:03:21
5529244620.03.202311:22:4900:07:5311:30:4200:07:53
5629242220.03.202310:15:1800:39:5010:55:08PC00:39:50
5729242020.03.202310:13:3503:52:1414:05:49PC03:52:14
mapa_cargas
Cell Formulas
RangeFormula
T2:T57T2=I2



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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Hi,
You can test following
VBA Code: 
Sub Test()
Dim ws As Worksheet
Set ws = Worksheets("mapa_cargas")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
Dim totalHours As Double
Dim count2 As Long, i As Long
Dim averageHours As Double
    
    For i = 2 To lastRow
        If ws.Cells(i, 20) <> "PC" Then
            totalHours = totalHours + ws.Cells(i, 20).Value * 24
            count2 = count2 + 1
        End If
    Next i
    
   averageHours = totalHours / count2
   Worksheets("indicadores").Cells(4, 7).Value = averageHours
End Sub
 
Upvote 0
arthurbr said:
Wouldn't the AVERAGEIFS function do the job?
Click to expand...
Maybe, but i have to resume some indicators in other sheet from that table that i have automated with vba, with one clic it fills the proper indicators all at once, every day i have to refresh that table with new data.
 
Upvote 0
aenemus said:
Maybe, but i have to resume some indicators in other sheet from that table that i have automated with vba, with one clic it fills the proper indicators all at once, every day i have to refresh that table with new data.
Click to expand...
Refreshing the data is not a problem provided your range is built as an Excel Table
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
329
awoohaw
awoohaw
S
  • Solved
Average in excel
Replies
3
Views
89
etaf
etaf
K
  • Question
conditional formatting question
Replies
2
Views
195
kerm007
K
J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
447
felixstraube
F
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
224
etaf
etaf

Forum statistics

Threads
1,215,635
Messages
6,125,945
Members
449,275
Latest member
jacob_mcbride

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top