I have a pivot table which contains target values. I want to get an average
of the targets excluding the zeros. I've included simplified data below.


Fleet Subfleet location Target
PG PG1 42090 3
PG NC700 42090 3
PG SLAB 42090 0

average should calculate to 3 not to 2....

In the dropdown filter for the 'Target' column, just untick the '0' and the
table will recalculate without the zero's (but it will also not display them)

If you need the '0' items to display then unfortunately I do not know the
answer. Someone else maybe able to help here.

I'd use a helper column with a formula like:

=if(d2=0,"",d2)

Then pivot on that.

Robin G wrote:

I have a pivot table which contains target values. I want to get an average
of the targets excluding the zeros. I've included simplified data below.

Fleet Subfleet location Target
PG PG1 42090 3
PG NC700 42090 3
PG SLAB 42090 0

average should calculate to 3 not to 2....

--

Dave Peterson