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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
167
SFCChase
SFCChase
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
274
tezza
tezza
J
  • Question Question
VBA: delete all rows where value (time) is less than 1 minute
2
Replies
13
Views
228
RobP
R
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
607
awoohaw
awoohaw

Forum statistics

Threads
1,224,814
Messages
6,181,120
Members
453,021
Latest member
Justyna P

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