Hi,
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?
Thanks
| Survey | Site | Species type | Count |
| 1 | a | Bird | 7 |
| 1 | a | Mammal | 3 |
| 1 | b | Amphibian | 4 |
| 1 | b | Mammal | 5 |
| 1 | c | Reptile | 6 |
| 1 | c | Bird | 1 |
| 1 | c | Mammal | 3 |
| | | | 29 |
