Google Answers Logo
View Question
Q: MS Excel pivot average excluding null values ( No Answer,   2 Comments )
Subject: MS Excel pivot average excluding null values
Category: Business and Money
Asked by: munderic-ga
List Price: $27.00
Posted: 20 Nov 2006 12:59 PST
Expires: 20 Nov 2006 18:14 PST
Question ID: 784313
I have a pivot table in MS Excel based on about 32000 rows of data. 
In the layout part of the MS Excel pivot wizard, there is a list of
data elements.  Double clicking the field reveals a list of functions.
 I am looking for a function that excludes null values from my
average.  I am the executive receiving the report, and I need to get
this done today.  My analyst is out of the office and I cannot reach
him.  I know my way around pivots a bit, but I cannot figure out how
to do this simply.  I need a fast fix because I don't have more than
15-30 minutes available to fix this.  If you have a fast fix let me
know; otherwise my analyst will fix tomorrow but I will be late with
this report.  Thanks, Chris
There is no answer at this time.

Subject: Re: MS Excel pivot average excluding null values
From: ken_tn-ga on 20 Nov 2006 15:40 PST
Are you sure that the average function used through Pivot tables
doesn't already ignore NULL values?  Or do you want it to ignore zero
values?  If you test a pivot table with less records but a few null
values, I think it will show that NULL values ARE already being

Subject: Re: MS Excel pivot average excluding null values
From: munderic-ga on 20 Nov 2006 18:14 PST
Ken is right about pivot ignoring null values.  The analyst has added
some kind of extra step, and is showing a page in the file that
includes null values in the count for the denominator.  However, the
page is some kind of values-output only from which I cannot derive
sources or formulae.  I'll have to wait for my analyst.  The extra
step is some other process that is including null values (I do not
mean zeroes).  Thanks, chris

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy