Pivot Table Avg only when >0.000?

[Ken]

Excel 2000 ... I am new to use of Pivot Tables, but liking
what I see as I am constantly crunching numbers ...

Above said ... I have a Pivot Table that is Averaging
numbers ... Issue is ... the Field often contains a 0.000
value which is ok, but this is skewing the Pivot Table
results. When I run into like problem on a spread sheet
then I set formula to = Sum of Field/values >0.

Is there a way to setup Pivot Table Average formula so a
0.000 will not be counted when it does its averaging?

Clear as mud I am sure ... Thanks to all who support this
board ... Kha

 

[guilbj2]

Hi Kha,

Hopefully this will be of some assistance. It's one of those thing
that will likely mean some time to setup depending on how you'v
structured your workbook, but it will definitely do what you want.

What you need to do is structure an IF condition to check if the valu
of the cell is greater than 0 (or if it exists at all) before adding i
to the total... I've included syntax for you below

IF(COUNTA(A1)>0,(put your formula here,"")

What this does is check if the value in cel A1 is greater than 0. I
it is, it will do whatever you put where I've written "put your formul
here". You also have the option of setting a course of action in cas
the cell is not greater than 0 by replacing the "" . If you leave th
"" it tells the program to do nothing.

I hope this is what you're looking for, but it will likely need som
adjustments depending on the design of your sheet. If you need som
help getting it sorted out, just reply to my post with specifics (cel
numbers where you have data, what your averaging, what formulas you'r
using... etc) and I'll do my best to help you sort it out

 

[Eric]

The quick an dirty way I get around this is to do a
find/replace in my raw data. I replace all the zeroes
with blanks. The pivot table will then ignore these items
in my average calculations.

Maybe not the best option, but it is definitely quick.

HTH

Eric

 

[Ken]

Excel 2000 ... More info.

I have 2 Fields on my spread sheet that may contain
BLANKS, ZEROS (0.000) or Values >0.000.

I would like the Pivot Table to only work on those Rows
where value in both Fields is >0.000. If either Field
contains a BLANK or 0.000 value then ignore both Fields.

I am new to use of Pivot Tables, but have considered idea
from 1st post as it is quick & I have approx 45,000
records that I am trying to sort out. However, if there
is a way to accomplish this with the Pivot Table setup or
formulas then I would like to learn it ... Thanks ... Kha

 

[Debra Dalgleish]

Add a field to your source table, in which you check the contents of the
two fields. For example, if the two fields are in columns E and F,
create a new column, and enter the following formula in row 2:

=OR(E2=0,F2=0)

Add the new field to the Page area of the pivot table, and select FALSE
from the page field dropdown.