[Previous] [Contents] [Next]

Sum, Average, Count, Min, Max, and More

So far, every example in this tutorial has involved summing data. It is also possible to get an average, minimum, or maximum of data. In VBA, change the Function property of the data field and give the data field a unique name. For example, the following code fragment produces five different summaries of the quantity field, each with a unique name:

    ' Set up the data fields
    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        .NumberFormat = "#,##0,K"
        .Name = "Total Revenue"
    End With

    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlCount
        .Position = 2
        .NumberFormat = "#,##0"
        .Name = "Number Orders"
    End With

    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlAverage
        .Position = 3
        .NumberFormat = "#,##0"
        .Name = "Average Revenue"
    End With

    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlMin
        .Position = 4
        .NumberFormat = "#,##0"
        .Name = "Smallest Order"
    End With

    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlMax
        .Position = 5
        .NumberFormat = "#,##0"
        .Name = "Largest Order"
    End With

The resultant pivot table provides a number of statistics about the average revenue, largest order, smallest order, and so on.

[Previous] [Contents] [Next]