Solved: Get average table for each month in Column L

[marshybid]

Hi All,

OK, so I've run my macro on the raw data sheet and get the result in attached spreadsheet (Example 2)

Now I would like to add some code that will do the following;

I need to create a table showing average time for each Month in column 'L' showing average time for each column that shows elapsed time (M, O, Q, R, T, V, X, Z, AA, AC, AD)

Any help gratefully received.

Thanks,

Marshybid

[Bob Phillips]

Do you mean

=IF(TRIM(L2)<>"",AVERAGE(IF(MOD(COLUMN(M2:AC2),2)=1,M2:AC2)),"")

[marshybid]

Hi xld, thanks for the reply.

I have written the following code which creates a pivot table and chart which seems to work OK



On Error Resume Next
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="'Cycle Times'!A:AF").CreatePivotTable _
TableDestination:="'Pivot Table'!R3C1", _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
Sheets("Pivot Table").Select
With ActiveSheet.PivotTables("PivotTable1")

PTSubtotals .PivotFields("Month Submitted")

.AddFields RowFields:=Array("Month Submitted")

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month Submitted")
.PivotItems(" ").Visible = False
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Request Submitted For Approval Elapsed Time")
.Orientation = xlDataField
.Caption = "Average of Request Submitted For Approval Elapsed Time"
.Position = 1
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Request Approval Elapsed Time")
.Orientation = xlDataField
.Caption = "Average of Request Approval Elapsed Time"
.Position = 2
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Request Publish To Supplier Elapsed Time")
.Orientation = xlDataField
.Caption = "Average of Request Publish To Supplier Elapsed Time"
.Position = 3
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Supplier Proposal Received Elapsed Time")
.Orientation = xlDataField
.Caption = "Average of Supplier Proposal Received Elapsed Time"
.Position = 4
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Supplier Proposal Converted To Draft Order Elapsed Time")
.Orientation = xlDataField
.Caption = _
"Average of Supplier Proposal Converted To Draft Order Elapsed Time"
.Position = 5
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Draft Order Published Elapsed Time")
.Orientation = xlDataField
.Caption = "Average of Draft Order Published Elapsed Time"
.Position = 6
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Supplier Accepted Draft Order Elapsed Time")
.Orientation = xlDataField
.Caption = "Average of Supplier Accepted Draft Order Elapsed Time"
.Position = 7
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Order Checked out for Approval Elapsed Time")
.Orientation = xlDataField
.Caption = "Average of Order Checked out for Approval Elapsed Time"
.Position = 8
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Approval Confirmation received from Tigers Elapsed Time")
.Orientation = xlDataField
.Caption = _
"Average of Approval Confirmation received from Tigers Elapsed Time"
.Position = 9
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Click Cycle Time")
.Orientation = xlDataField
.Caption = "Average of Click Cycle Time"
.Position = 10
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Tiger Cycle Time")
.Orientation = xlDataField
.Caption = "Average of Tiger Cycle Time"
.Position = 11
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total Cycle Time")
.Orientation = xlDataField
.Caption = "Average of Total Cycle Time"
.Function = xlAverage
End With
Columns("C:C").Select
Selection.NumberFormat = "d:h:m"
End With
On Error GoTo 0

Range("A4").Select
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Pivot Table").Range("A4")
ActiveChart.Location Where:=xlLocationAsNewSheet


Application.ScreenUpdating = True


End Sub


This seems to give me what I need for now.

Thanks,

Marshybid