Averaging values in a column with conditions in two other columns

D

DPECK

New Member
Joined
Sep 4, 2014
Messages
31
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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Welcome to the board.

Do you need to use VBA? If not, you can easily achieve this using the AVERAGEIFS function.
 
Upvote 0
bbott said:
Welcome to the board.

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


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:p></o:p>
 
Upvote 0
Take a look at this example. It will place the average of Comps for Crew 1 for rows 2 through 10 in cell A1:
Code: 
Sub AveIfs()

    Range("A1") = WorksheetFunction.AverageIfs(Range("AE2:AE10"), Range("H2:H10"), _
        "Comp", Range("AH2:AH10"), "Crew 1")
    
End Sub
 
Upvote 0
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.

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

[TABLE="width: 227"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]FAC GENERAL MAINT[/TD]
[TD="align: right"]4.6[/TD]
[/TR]
[TR]
[TD]FAC PROCESS MAINT[/TD]
[TD="align: right"]135.5333333[/TD]
[/TR]
[TR]
[TD]FIRE PROTECTION[/TD]
[TD="align: right"]196[/TD]
[/TR]
[TR]
[TD]FAC ELECTRICAL[/TD]
[TD="align: right"]94.03333333[/TD]
[/TR]
</tbody>[/TABLE]

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

Code: 
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
 
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Calculate average of values based on values in other column
Replies
3
Views
298
arthurbr
arthurbr
C
  • Solved
If number is between two averages, Then this value
Replies
4
Views
360
dashiellx
D
J
  • Question Question
VBA: Conditional Formatting one column based on another
Replies
2
Views
428
jazzhayward
J
J
  • Solved
Applying conditional formatting to one column based on values in two other columns
Replies
5
Views
813
Fluff
Fluff
nicolews
  • Question Question
Conditional Formatting based on other cell values
Replies
3
Views
985
ERINWILLIS01
E

Forum statistics

Threads
1,224,315
Messages
6,177,842
Members
452,809
Latest member
mar_luna

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