Is there a way to Average a PivotTable column, but to exclude factoring in zeros from the raw data?
Example:
[pre]
Raw Data
Quantity Sales Cost Profit %Profit
1 $10 $4 $6 60%
3 $30 $12 $18 60%
2 $20 $8 $12 60%
0 $0 $0 $0 0%
1 $10 $4 $6 60%
0 $0 $0 $0 0%
[/pre]
When the Sales are summed on the PivotTable, I would expect to see an overall %Profit of 60%. However, when I Average the %Profit column on the PivotTable, the 0%'s are factored in resulting in skewed %Profit.
For the example above it would result in 40% on the PivotTable: (60+60+60+0+60+0)/6
I need the zeros excluded to result in 60%: (60+60+60+60)/4
Thoughts?
Example:
[pre]
Raw Data
Quantity Sales Cost Profit %Profit
1 $10 $4 $6 60%
3 $30 $12 $18 60%
2 $20 $8 $12 60%
0 $0 $0 $0 0%
1 $10 $4 $6 60%
0 $0 $0 $0 0%
[/pre]
When the Sales are summed on the PivotTable, I would expect to see an overall %Profit of 60%. However, when I Average the %Profit column on the PivotTable, the 0%'s are factored in resulting in skewed %Profit.
For the example above it would result in 40% on the PivotTable: (60+60+60+0+60+0)/6
I need the zeros excluded to result in 60%: (60+60+60+60)/4
Thoughts?