Averages in Pivot Table

[cfcanter]

I have a table set up as follows (partial table)
LotID Part CycleTime in Hours Process SuperPart
A1 12N 100 SP522 06123
A2 12N 110 SP522 06123
A3 12N 112 SP522 06123
A8 11N 90 SP522 96123
A4 03F 60 BN110 07999

I need to convert cycle time from hours to days and average them in a pivot table. Pivot table should look like this:

Process Superpart Part Avg CycleTime in Days
SP522 06124 12N 4.5
96123 11N 3.8
BN110 07999 03F 2.5

I am having trouble getting the pivot table to average using VBA code. Below is my code that I am using:

Sub CyclePivot()
'this is a Pivot Table for cycle time analysis
'written by C. Canter 4-26-02
'
Dim ptcache As PivotCache
Dim pt As PivotTable
Application.ScreenUpdating = False
'delete pivotsheet if exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("CycleTime&quot.Delete
On Error GoTo 0
Worksheets.Add
ActiveSheet.Name = "CycleTime"
Sheets("CycleData&quot.Select
'
'create a pivot table
Set ptcache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Range("A1&quot.CurrentRegion.Address)
'
'Create the pivot table from the cache
Set pt = ptcache.CreatePivotTable( _
TableDestination:=Sheets("CycleTime&quot.Range("A1&quot, _
TableName:="CyclePivot&quot
With pt
' add row fields
.PivotFields("PROCESS&quot.Orientation = xlRowField
.PivotFields("SUPERPART&quot.Orientation = xlRowField
.PivotFields("PARTNAME&quot.Orientation = xlRowField
' add calculated fields
.CalculatedFields.Add "Active Days", "=((TOTALLEADTIME - TOTALHELDTIME)/24)"
.CalculatedFields.Add "Days on Hold", "=(TOTALHELDTIME/24)"
.CalculatedFields.Add "Total Cycle Time", "=(TotalLeadTime/24)"
.PivotFields("Active Days&quot.Orientation = xlDataField
.PivotFields("Days on Hold&quot.Orientation = xlDataField
.PivotFields("Total Cycle Time&quot.Orientation = xlDataField
.PivotFields("Active Days&quot.Function = xlAverage
.PivotFields("Days on Hold&quot.Function = xlAverage
.PivotFields("Total Cycle Time&quot.Function = xlAverage

.PivotFields("PROCESS&quot.Subtotals = _
Array(False, False, False, True, False, False, False, False, False, False, False, False)
.PivotFields("SUPERPART&quot.Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("PARTNAME&quot.Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Active Days&quot.NumberFormat = "#,##0"
.PivotFields("Days on Hold&quot.NumberFormat = "#,##0"
.PivotFields("Total Cycle Time&quot.NumberFormat = "#,##0"
End With
' Change the positions in the table
Sheets("CycleTime&quot.Select
Range("D1&quot.Select
With ActiveSheet.PivotTables("CyclePivot&quot.PivotFields("Data&quot
.Orientation = xlColumnField
.Position = 1
End With
Application.CommandBars("PivotTable&quot.Visible = False
Application.ScreenUpdating = True
End Sub

I am not getting an error message - xl is only summing the data not averaging. Any suggestions is appreciated.

Thank you
Craig Canter
Cost Accounting
Sipex

"who dares wins! And gets more assignments." Craig Canter
Cost Accountant
Sipex
Milpitas, CA