Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel PivotTable - Average Excluding Zeros

Status
Not open for further replies.

ryplew

Technical User
Jun 3, 2015
18
US
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?
 
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.
 
This is a calculation outside the PivotTable, so for the count, use the COUNTIF() function.
 
Add this field again as page field, right-click it and in "hide elements" option select 0.

combo
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top