In this tech support guide, we will walk you through the process of calculating the average of columns including blanks in a PivotTable with 10 plant species observed at individual sub-sites. The PivotTable may contain plant species that appear in more than one sub-site.
Prerequisites
Before we begin, ensure that you have the following:
- Excel 2010 or later version
- Data in a tabular format with plant species and sub-site information
Step 1: Create a PivotTable
First, create a PivotTable using the data. Select the range of data and go to the "Insert" tab, then click "PivotTable" and choose where you want to place the PivotTable.
Step 2: Set up the PivotTable
Drag the plant species column to the "Rows" area and the sub-site column to the "Columns" tab. The value column should be dragged to the "Values" area.
Step 3: Calculate the Sum of Values
By default, the PivotTable calculates the sum of values. This will include blank cells as well.
Step 4: Calculate the Average
To calculate the average, right-click on any cell in the "Values" area and select "Value Field Settings". In the dialog box, change the calculation type to "Average" and click "OK".
Step 5: View the Results
The PivotTable will now display the average of the values for each plant species at each sub-site, including blank cells.
Code Example
Here's an example of how to create a PivotTable using VBA:
Sub CreatePivotTable()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Add(SourceType:=xlDatabase, SourceData:= _
Range("A1:C100"), TableDestination:=Range("H1:H103"))
With pt
.Name = "PivotTable1"
Set pf = .PivotFields("PlantSpecies")
pf.Orientation = xlRowField
Set pi = pf.PivotItems("PlantSpecies1")
pi.Visible = False
End With
Set pf = .PivotFields("SubSite")
pf.Orientation = xlColumnField
Set pi = pf.PivotItems("SubSite1")
pi.Visible = False
Set pi = pf.PivotItems("SubSite2")
pi.Visible = False
.ManualUpdate = True
End Sub
Calculating the average of columns including blanks in a PivotTable is a straightforward process. By following the steps outlined above, you can easily calculate the average of values for each plant species at each sub-site, including blank cells.