CalculatedFields in Pivots
 

Hi,
In the following simple example I am trying to calculate
the average distance between customer locations. The
CalculatedFields fails to provide the correct average
because despite using xlCountNums in the "Op No." field,
when it is calculated the CalculatedFields function (only)
uses xlSum instead. Could anyone please show me where I
have gone wrong?
Appreciate any help.

T.I.A

Geoff

Example data and code below:

Comp Loc Comp Loc Op Kms Op No.
Jones Loc1 Smith Loc2 7 1
Smith Loc2 Jones Loc1 13 2
Jones Loc1 Jones Loc3 3 3
Jones Loc3 Jones Loc1 8 4
Jones Loc1 Jones Loc3 2 5
Jones Loc3 Jones Loc1 2 6
Jones Loc1 Brown Loc4 28 1
Brown Loc4 Smith Loc2 4 2
Smith Loc2 Green Loc5 3 3
Green Loc5 Jones Loc1 6 4

Sub aaaa()
With Sheets(1).Range("H1")
.Formula = "SOURCE - Av Kms by Customer"
.Font.Bold = True
End With
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=Sheets(1).Range
("A1").CurrentRegion.Address).CreatePivotTable _
TableDestination:=Sheets(1).Range("H3"), _
TableName:="PivotTable1"
With Sheets(1).PivotTables("PivotTable1")
.PivotFields("Comp").Subtotals = Array _
(False, False, False, False, False, False, False,
False, False, False, False, False)
.PivotFields("Comp2").Subtotals = _
Array(False, False, False, False, False, False,
False, False, False, False, False, False)

.AddFields RowFields:=Array("Comp", "Loc", "Data"),
ColumnFields:=Array("Comp2", "Loc2")

.GrandTotalName = "Total "
.ErrorString = ""
.DisplayErrorString = True

With .PivotFields("Op Kms")
.Orientation = xlDataField
.Caption = "Operation Kms"
.Function = xlSum
End With
With .PivotFields("Op No.")
.Orientation = xlDataField
.Caption = "Journeys"
.Function = xlCountNums
End With

.CalculatedFields.Add "Field1", "=Op Kms/Op No.", True
With .PivotFields("Field1")
.Orientation = xlDataField
.Caption = "Av Kms"
.NumberFormat = "#,##0.0"
End With
End With
End Sub