Excel Pivot Table

J

Jeff

Hi all,

I have a pivot table where I created a calculated field that averages three
of the fields in the pivot table. My problem is that it averages the "blank"
(which are created from blank fields in the source data) fields instead of
ignoring them. How can I average the fields in a Pivot Table and not include
the blanks in the average?
 
P

Paul

Use
=Sum(F1,F2,F3..)/Count(F1,F2,F3...)
Blank cells will add nothing to the Sum total, and Count will only count the
cells with numbers; hence you can obtain the average for the numbered cells.

Paul
 
S

Shane Devenshire

Hi,

Try this, with the pivot table selected choose PivotTable, Table Option and
check For Empty cells show, don't put anything in the adjacent box and click
OK.
 
J

Jeff

Paul,

Thanks so much for your quick response. However it did not resolve my
problem. Here are the results:
A B C Should be Results
1 0.75 1 0.9167 91.67%
(blank) 0.75 1 0.875 58.33%
1 0.5 1 0.8333 83.33%
1 0.5 0 0.5 50.00%
1 1 1 1 100.00%
(blank) 1 1 1 66.67%
1 1 1 1 100.00%

If you have any other suggestions I would appreciate it very much.

Thanks,

Jeff
 
J

Jeff

Shane,

Thanks for the suggestion. However, it did not resolve my issue. It was
already set up the way you suggested.

Take care,

Jeff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top