Averaging values in a column with conditions in two other columns

[DPECK]

I have a 57 column, 8832 row spread sheet that I'm extracting data from and creating tables and charts. I'm wanting to calculate the average number of days it took to complete each work order. I have a column with the "days to complete" (col AE) and the conditions that I want to meet are status (col H) = Comp and CrewId (this will change with the crew. Elect, I&C, FireSys... )(col AH). How do I write the VBA code for this?

 

[bbott]

Welcome to the board.

Do you need to use VBA? If not, you can easily achieve this using the AVERAGEIFS function.

 

[DPECK]

Welcome to the board.

Do you need to use VBA? If not, you can easily achieve this using the AVERAGEIFS function.

My preference is to use VBA because I'm automating the report generation, data sorting and chart creation. As well as posting the charts to an intranet web page<o></o>

 

[bbott]

Take a look at this example. It will place the average of Comps for Crew 1 for rows 2 through 10 in cell A1:

Sub AveIfs()

    Range("A1") = WorksheetFunction.AverageIfs(Range("AE2:AE10"), Range("H2:H10"), _
        "Comp", Range("AH2:AH10"), "Crew 1")
    
End Sub

 

[DPECK]

Thank you for your response. Here is a sample of the data I'm working with. On another worksheet with in this workbook in a column and the respective row for each crew is where I want the information to go. Averaging "Days To Complete" based on the status of "Comp" excluding work types "PM" and "PdM" for each crew.

WONUM	WORKTYPE	STATUS	TARGCOMPDATE	ACTFINISH	DAYS TO COMPLETE	REPORTDATE	CREWID	ACTLABHRS	Days from Due	Due Status	days from reported
11030162	CM	COMP	2014-08-31 01:14:01	2014-08-04 07:20:34	3.3	2014-08-01 01:13:35	FAC GENERAL MAINT	2.0	4	overdue	33
11032961	CM	COMP	2014-08-15 09:56:01	2014-08-07 07:35:29	5.9	2014-08-01 09:55:20	FAC GENERAL MAINT	2.0	19	overdue	33
11026712	CM	WMATL	2014-09-24 04:31:49		-41849.2	2014-07-29 04:30:59	FAC GENERAL MAINT	3.0	-20		35
11044547	CM	APPR	2014-09-06 15:11:20		-41858.6	2014-08-07 15:11:20	FAC GENERAL MAINT	3.3	-2	due in 5 days	27
11026639	PM	ACCEPT	2014-09-29 04:00:00		-41849.1	2014-07-29 02:11:25	FAC GENERAL MAINT	0.0	-25		35
11028572	PM	ACCEPT	2014-09-29 04:00:00		-41850.1	2014-07-30 02:16:14	FAC GENERAL MAINT	0.0	-25		34
10336110	CM	COMP	2014-07-31 06:42:17	2014-07-21 14:46:46	356.3	2013-07-30 06:42:17	FAC PROCESS MAINT	0.0	34	overdue	394
10868927	CM	COMP	2014-06-04 16:22:50	2014-05-06 15:38:59	1.0	2014-05-05 16:22:50	FAC PROCESS MAINT	0.0	90	overdue	119
10870549	CM	COMP	2014-05-16 12:27:25	2014-05-12 06:46:13	5.7	2014-05-06 13:26:07	FAC PROCESS MAINT	0.0	108	overdue	118
10882131	CM	COMP	2014-06-27 13:50:20	2014-06-02 15:10:48	19.1	2014-05-14 13:50:20	FAC PROCESS MAINT	0.0	67	overdue	110
11073252	CM	INPRG	2014-09-12 10:41:25		-41876.4	2014-08-25 10:41:25	FAC PROCESS MAINT	0.0	-8		9
11074872	CM	INPRG	2014-09-02 11:42:34		-41877.5	2014-08-26 11:42:34	FAC PROCESS MAINT	0.0	2	overdue	8
11033092	CM	OPEN	2014-09-26 15:54:20		-41852.7	2014-08-01 15:54:20	FAC PROCESS MAINT	0.0	-22		33
11036568	PM	ACCEPT	2014-09-02 04:00:00		-41854.1	2014-08-03 02:03:25	FAC PROCESS MAINT	0.0	2	overdue	31
11037080	PM	ACCEPT	2014-09-03 03:30:00		-41855.1	2014-08-04 02:01:29	FAC PROCESS MAINT	0.0	1	overdue	30
11041451	PM	WMATL	2014-08-31 10:00:00		-41856.5	2014-08-05 12:46:05	FAC PROCESS MAINT	0.0	4	overdue	29
11056773	PM	WMATL	2014-09-14 15:24:11		-41866.6	2014-08-15 15:24:11	FAC PROCESS MAINT	1.0	-10		19
10913184	PM	INPRG	2014-08-31 10:00:00		-41791.1	2014-06-01 02:01:43	FAC PROCESS MAINT	7.5	4	overdue	93
10857223	PM	COMP	2014-05-31 10:00:00	2014-05-26 14:35:53	26.5	2014-04-30 02:04:20	FAC PROCESS MAINT	8.0	94	overdue	124
10857246	PM	COMP	2014-05-31 10:00:00	2014-05-26 14:36:19	26.5	2014-04-30 02:04:38	FAC PROCESS MAINT	8.0	94	overdue	124
11046891	PM	INPRG	2014-08-29 10:00:00		-41859.4	2014-08-08 10:03:52	FAC PROCESS MAINT	12.0	5	overdue	26
10837343	PM	COMP	2014-05-18 10:00:00	2014-05-15 15:17:36	27.5	2014-04-18 02:07:30	FAC PROCESS MAINT	23.5	106	overdue	136
11046871	PM	INPRG	2014-08-29 02:00:00		-41859.4	2014-08-08 10:00:27	FAC PROCESS MAINT	26.0	5	overdue	26
11047015	RM	ACCEPT	2014-09-07 14:49:31		-41859.6	2014-08-08 14:49:31	FAC PROCESS MAINT	0.0	-3	due in 5 days	26
11055510	RM	ACCEPT	2014-09-13 19:37:05		-41865.8	2014-08-14 19:37:05	FAC PROCESS MAINT	0.0	-9		20
10254370	RM	COMP	2014-09-19 15:33:33	2014-08-21 07:26:53	424.1	2013-06-23 04:35:50	FAC PROCESS MAINT	0.0	-15		431
11033063	RM	COMP	2014-08-08 15:19:59	2014-08-08 15:10:56	7.0	2014-08-01 15:19:59	FAC PROCESS MAINT	0.0	26	overdue	33
10457657	CP	COMP		2014-02-26 16:42:30	153.0	2013-09-26 17:43:07	FIRE PROTECTION	19.0	0	#N/A	338
9839413	CP	COMP	2011-07-13 09:19:49	2013-07-25 11:45:40	239.0	2012-11-28 10:53:44	FIRE PROTECTION	148.5	1131	overdue	636
11056935	PM	ACCEPT	2014-09-15 03:00:00		-41867.1	2014-08-16 02:02:39	FIRE PROTECTION	0.0	-11		18
11066453	PM	ACCEPT	2014-08-29 02:30:00		-41874.1	2014-08-23 02:07:50	FIRE PROTECTION	0.0	5	overdue	11
11056617	CM	ACCEPT	2014-09-14 08:35:15		-41866.4	2014-08-15 08:35:15	FAC ELECTRICAL	0.0	-10		19
11076920	CM	ACCEPT	2014-09-26 09:31:28		-41878.4	2014-08-27 09:31:28	FAC ELECTRICAL	0.0	-22		7
10967280	CM	APPR	2014-08-31 15:02:00		-41815.6	2014-06-25 15:02:36	FAC ELECTRICAL	0.0	4	overdue	69
10481786	CM	COMP	2014-05-31 10:07:36	2014-06-12 11:55:10	246.9	2013-10-08 15:19:32	FAC ELECTRICAL	0.0	94	overdue	326
10828770	CM	COMP	2014-05-12 00:11:02	2014-05-09 10:42:55	27.4	2014-04-12 00:11:02	FAC ELECTRICAL	0.0	112	overdue	142
10839906	CM	COMP		2014-04-26 16:07:00	7.8	2014-04-18 20:41:34	FAC ELECTRICAL	0.0	0	#N/A	136
10757622	PM	PENSCHED	2014-09-17 11:40:00		-41701.4	2014-03-03 10:40:51	FAC ELECTRICAL	0.0	-13		181
9987019	PM	PENSCHED	2014-09-12 06:00:00		-41315.1	2013-02-10 02:02:23	FAC ELECTRICAL	0.0	-8		564
10813853	PM	WMATL	2014-07-15 04:00:00		-41732.1	2014-04-03 02:05:47	FAC ELECTRICAL	0.0	49	overdue	151
10821140	PM	WMATL	2014-07-15 04:00:00		-41737.1	2014-04-08 02:07:51	FAC ELECTRICAL	0.0	49	overdue	146
11053961	PM	WSCH	2014-08-28 04:00:00		-41865.1	2014-08-14 02:12:16	FAC ELECTRICAL	0.0	6	overdue	20
10602175	PM	COMP	2014-09-30 06:00:00	2014-05-30 12:34:52	168.4	2013-12-13 02:09:56	FAC ELECTRICAL	1.0	-26		261
11004659	PM	PENSCHED	2014-07-16 04:00:00		-41836.1	2014-07-16 02:16:22	FAC ELECTRICAL	1.0	48	overdue	48
9858409	PM	INPRG	2014-07-05 06:00:00		-41250.1	2012-12-07 02:08:05	FAC ELECTRICAL	2.3	59	overdue	627
10215179	PM	INPRG	2014-01-31 10:00:00		-41427.1	2013-06-02 02:10:53	FAC ELECTRICAL	2.5	214	overdue	452

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><TBODY>
</TBODY>

 

[bbott]

Do you have a static list of crew IDs or will that vary?

 

[DPECK]

It will vary

 

[bbott]

I copied your sample table into sheet 1 A1:L47. Here are the results of the averages:

FAC GENERAL MAINT	4.6
FAC PROCESS MAINT	135.5333333
FIRE PROTECTION	196
FAC ELECTRICAL	94.03333333

<colgroup><col><col></colgroup><tbody>
</tbody>

Here is the code I used. It will need to be adjusted to your actual data:

Sub CrewAve()

    Dim x As Long
    Application.ScreenUpdating = False
    
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Crew Averages"
    Range("A1") = "Crew ID"
    Range("B1") = "Average Days"
    
    For x = 2 To Sheets(1).Range("H" & Rows.Count).End(xlUp).Row
        If Sheets(1).Range("H" & x) <> Sheets(1).Range("H" & x).Offset(-1, 0) Then
            Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0) = Sheets(1).Range("H" & x)
        End If
    Next x
    
    For x = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Range("B" & x) = WorksheetFunction.AverageIfs(Sheets(1).Range("F:F"), _
        Sheets(1).Range("H:H"), Range("A" & x), _
        Sheets(1).Range("C:C"), "Comp", _
        Sheets(1).Range("B:B"), "<>PM", _
        Sheets(1).Range("B:B"), "<>PdM")
    Next x
    
    Columns("A:B").AutoFit
    Application.ScreenUpdating = True
    
End Sub

 

[DPECK]

Thank you! I will give it a go

 

[DPECK]

The code below works through the first two loops then I get a Run-time error '13': Type mismatch (shown red text). If I remove the DueStatus portion of the two loops that it errors in the remaining 6 loops deliver the desired data and the two modified loops follow the remaining code. I'm lost on this problem.

Sub OVERDUE_DWO()


Dim x As Integer
Dim area As String
Dim PastDue As Long
Dim i As Long
Dim status As String
Dim workType As String
Dim DueStatus As String


For x = 1 To 9
area = Sheets("DWOs").Cells(x, 1).Value
PastDue = 0
If area = "FAC PROCESS MAINT" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue

ElseIf area = "FAC GENERAL MAINT" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue

ElseIf area = "FAC ELECTRICAL" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue

ElseIf area = "FAC FIRESYS" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue

ElseIf area = "FAC FMCS" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue

ElseIf area = "FAC BALANCE" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue

ElseIf area = "FAC PREDICTIVE" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue

ElseIf area = "FIRE PROTECTION" Then
i = 1
Do Until IsEmpty(Sheets("DATA").Cells(i, 34))
If Sheets("DATA").Cells(i, 34).Value = area Then
status = Sheets("DATA").Cells(i, 8).Value
workType = Sheets("DATA").Cells(i, 7).Value
If Not status = "COMP" And Not status = "HOLD" And Not status = "" And Not workType = "PM" _
And Not workType = "PDM" And Not workType = "CP" And Not workType = "" Then
DueStatus = Sheets("DATA").Cells(i, 56).Value
If DueStatus = "Overdue" Then
PastDue = PastDue + 1
End If
End If
End If
i = i + 1
Loop
Sheets("DWOs").Cells(x, 3).Value = PastDue

End If
Next x
End Sub