Pivot table average calculations

Copper Contributor



I am using pivot tables to display and calculate data and I'm looking for the simplest way to calculate averages. 


I would like to calculate the average for species type per site. For example, the calculation for birds should be (7 + 1) / 3.


I have tried using average in the pivot table, but it calculates the average for birds as (7 + 1) / 2. This isn't the result that I want as the denominator doesn't consider all sites (i.e. a zero count for birds in site b).


I have tried using distinct count and count rows in an attempt to fix the denominator. This seems to work fine for the overall average i.e. 7+3+4+5+6+1+3) / 3. However, when I add species type into the pivot table it reverts back to ignoring zero counts. 


Is there a simple setting that will force excel to assume zero counts or alternatively is there a measure or number of measures that I can use to calculate this accurately?




SurveySiteSpecies typeCount


1 Reply

@AntonXcel The Microsoft Learn community is for Learn and certification related questions. You can direct any questions relating to Excel here: Microsoft Excel Community