Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Pivot Table
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Pivot Table
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 "Jeff" <Jeff @discussions.microsoft.com wrote in message ... 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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Pivot Table
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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Jeff" wrote: 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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Pivot Table
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 "Paul" wrote: 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 "Jeff" <Jeff @discussions.microsoft.com wrote in message ... 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? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Pivot Table
Shane,
Thanks for the suggestion. However, it did not resolve my issue. It was already set up the way you suggested. Take care, Jeff "Shane Devenshire" wrote: 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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Jeff" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Export Pivot Table Data to Excel Table | Excel Discussion (Misc queries) | |||
Pivot Table in Excel 2007 : Problem with % in Table | Excel Discussion (Misc queries) | |||
Excel Pivot Table Plugin? (crosstab to data table) | Excel Discussion (Misc queries) | |||
Can links between Excel 2003 Pivot Charts and their pivot table b. | Charts and Charting in Excel |