• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 12. Using VBA to Create Pivot Ta... > Sum, Average, Count, Min, Max, and M...

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

So far, every example in this chapter 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


					  


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint