Hi,
I'm trying to figure out why my pivot table is calculating a different average than when I do it manually.
In the attached file (View attachment Average # suppliers.xls), the table lists an average number of suppliers for businesses in a particular district of a country. I have always assumed that the "grand total" at the bottom is simply the average of the averages. When I look at that cell, I see that it's not entered as a formula - it's just the number. As I planned on playing with the data, I pasted the table into a new sheet and then changed that cell to a formula to calculate the average of the column.
Now that I've changed that cell to a formula, it gives an average of 6.9. If you manually calculate, the average also comes out to 6.9.
I've checked for blank entries in the original dataset, checked that all the numbers are exactly the same, and tried changing the entries so they had 8 decimal places instead of just 1 decimal place, etc. I can't figure out why the two totals are different.
So I suppose my question is twofold:
1. Does anyone have ideas why these numbers are coming out different from each other?
2. Does anyone know how the pivot table is calculating the 'grand total'? Perhaps my assumption that it should average all the averages is wrong?
Thanks.
I'm trying to figure out why my pivot table is calculating a different average than when I do it manually.
In the attached file (View attachment Average # suppliers.xls), the table lists an average number of suppliers for businesses in a particular district of a country. I have always assumed that the "grand total" at the bottom is simply the average of the averages. When I look at that cell, I see that it's not entered as a formula - it's just the number. As I planned on playing with the data, I pasted the table into a new sheet and then changed that cell to a formula to calculate the average of the column.
Now that I've changed that cell to a formula, it gives an average of 6.9. If you manually calculate, the average also comes out to 6.9.
I've checked for blank entries in the original dataset, checked that all the numbers are exactly the same, and tried changing the entries so they had 8 decimal places instead of just 1 decimal place, etc. I can't figure out why the two totals are different.
So I suppose my question is twofold:
1. Does anyone have ideas why these numbers are coming out different from each other?
2. Does anyone know how the pivot table is calculating the 'grand total'? Perhaps my assumption that it should average all the averages is wrong?
Thanks.