Hi,
I am trying to calculate averages per site requiring use of the distinct count function. I've used the following calculations to determine these averages:
- Total count (calculated using the sum total) - Column 2
- Survey count (calculated using distinct count) - Column 3
- Average count (calculated as column 2/column 3).
This provides an accurate calculation for SLT01 and SLT02, but I would like the grand total to divide by 14 rather than 7. Is there a way to determine the distinct count of a column using criteria in a second column? I think this may help.
I would also like to calculate the average count for each species detected in a survey (e.g., SLT01). As the number of surveys remains the same all individual species counts must still be divided by 7 (though this may change in future surveys). However, when I select the species, rather than still dividing the total count for each species by the total survey count, the pivot table instead divides the total count by the number of surveys the species was detected (refer table below).
Is there a simple way of making the pivot table run the calculation in this way? I think the easiest way is probably to add an additional column in the dataset, which is an n value (i.e., number of sites per survey), but I'm curious to see if there is a workaround within the pivot table field calculation. Datasets that I receive may not readily have this information included.
I am trying to calculate averages per site requiring use of the distinct count function. I've used the following calculations to determine these averages:
- Total count (calculated using the sum total) - Column 2
- Survey count (calculated using distinct count) - Column 3
- Average count (calculated as column 2/column 3).
This provides an accurate calculation for SLT01 and SLT02, but I would like the grand total to divide by 14 rather than 7. Is there a way to determine the distinct count of a column using criteria in a second column? I think this may help.
| Row Labels | Total Count - Low tide survey event | Low tide survey count | Low tide average count per transect |
| SLT01 | 107 | 7 | 15.28571429 |
| SLT02 | 36 | 7 | 5.142857143 |
| Grand Total | 143 | 7 | 20.42857143 |
I would also like to calculate the average count for each species detected in a survey (e.g., SLT01). As the number of surveys remains the same all individual species counts must still be divided by 7 (though this may change in future surveys). However, when I select the species, rather than still dividing the total count for each species by the total survey count, the pivot table instead divides the total count by the number of surveys the species was detected (refer table below).
| Row Labels | Total Count - Low tide survey event | Low tide survey count | Low tide average count per transect |
| SLT01 | |||
| Charadrius mongolus | 3 | 2 | 1.50 |
| Charadrius ruficapillus | 26 | 5 | 5.20 |
| Chroicocephalus novaehollandiae | 3 | 2 | 1.50 |
| Ephippiorhynchus asiaticus | 1 | 1 | 1.00 |
| Esacus magnirostris | 4 | 3 | 1.33 |
| Gelochelidon nilotica | 5 | 3 | 1.67 |
| Haematopus longirostris | 9 | 5 | 1.80 |
| Hydroprogne caspia | 2 | 2 | 1.00 |
| Numenius madagascariensis | 17 | 6 | 2.83 |
| Numenius phaeopus | 19 | 4 | 4.75 |
| Pluvialis fulva | 9 | 2 | 4.50 |
| Pluvialis squatarola | 8 | 2 | 4.00 |
| Tringa nebularia | 1 | 1 | 1.00 |
| SLT02 | |||
| (blank) | 6 | 1 | 6.00 |
| Charadrius ruficapillus | 6 | 2 | 3.00 |
| Esacus magnirostris | 1 | 1 | 1.00 |
| Haematopus longirostris | 2 | 1 | 2.00 |
| Nil observed | 0 | 3 | 0.00 |
| Numenius madagascariensis | 6 | 1 | 6.00 |
| Numenius phaeopus | 15 | 4 | 3.75 |
| Grand Total | 143 | 7 | 20.42857143 |
Is there a simple way of making the pivot table run the calculation in this way? I think the easiest way is probably to add an additional column in the dataset, which is an n value (i.e., number of sites per survey), but I'm curious to see if there is a workaround within the pivot table field calculation. Datasets that I receive may not readily have this information included.
