Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivots/Charts | Charts and Charting in Excel | |||
Pivots and Functions | Excel Worksheet Functions | |||
several pivots | Excel Discussion (Misc queries) | |||
Formats and Pivots | Excel Discussion (Misc queries) | |||
Automatic pivots | Excel Discussion (Misc queries) |