Excel PivotTable - Average Excluding Zeros

[ryplew]

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?

 

[SkipVought]

Why does your source data include data for nothing? Exclude that!

 

[ryplew]

It needs to be there as, within the report, I am comparing this year's sales against last. So elsewhere, in another column, there are sales for the prior year where there were zero for this year. I only need to show %Profit for this year. Above was just an example of a portion of the data - my apologies for omitting an explanation for the zeros.

 

[SkipVought]

This is a calculation outside the PivotTable, so for the count, use the COUNTIF() function.

 

[combo]

Add this field again as page field, right-click it and in "hide elements" option select 0.

combo