# Thread: Pivot Table Sum (2000/SR1)

1. ## Pivot Table Sum (2000/SR1)

I would like to calculate an average of the sums in my Pivot table. However, the Pivot table puts a zero (0) in the field if the table from which it's generated contains a blank. As you know, when you calculate an average on a range of cells, any blanks are ignored. Since the pivot table generates a zero, my averages are off.

Can anyone tell me how generate a Pivot Table so that it leaves the cell blank instead of generating a zero?

2. ## Re: Pivot Table Sum (2000/SR1)

It's not clear how you are generating your Averages, i.e. with a Pivot Table field or with an out side calculation.

However try setting the PT options to reflect the attached graphic, and see if that helps.

Andrew C

3. ## Re: Pivot Table Sum (2000/SR1)

=sumif(SUMS,"<>0")/countif(SUMS,"<>0)

where SUMS is the range of sums from the pivot table.

Steve

4. ## Re: Pivot Table Sum (2000/SR1)

This option only changes the blank cells in the existing PT. I've attached a sample file so you can see what's happening. If you look at cell B11 on the "Pivot Table" sheet, there's a zero. But if you look at the "Data" sheet from which the PT is generated, cell C12, there is no data for that cell. When I generate an average from the PT the zero is counted thus throwing off my average.

Does that make sense?

5. ## Re: Pivot Table Sum (2000/SR1)

This won't necessarily work because if I have a valid zero, it will be discounted -- but thanks for the suggestion.

6. ## Re: Pivot Table Sum (2000/SR1)

What is the difference in meaning/entry between a "blank" and a "zero"? Doesn't a blank entry IMPLY None or zero?

I used the "for empty cells" trick and replaced with <alt>0001 (on num keypad) and it seems to work fine. count only counts the numbers not the blanks, since the blanks turn into text in the PT.

Steve

7. ## Re: Pivot Table Sum (2000/SR1)

A blank and zero don't necessarily mean the same thing. For example, suppose I set up a series of questions with the answers being 0=no, 1=yes, and blank=no response. Now I want to compute the total number of 0s and 1s but I don't want to count those answers that are blank. You can see that if I count the blanks as zero, the accuracy of my no answers will be off.

Does that help?

8. ## Re: Pivot Table Sum (2000/SR1)

I'm not sure I understand your "for empty cells" trick. Would you explain that in a bit more detail?

9. ## Re: Pivot Table Sum (2000/SR1)

You could use no = -1, yes = 1, no response = blank = 0

Steve

10. ## Re: Pivot Table Sum (2000/SR1)

I was referring to Andrew Cronnolly, post before mine ( [203859] ) which had you mark the "for empty cells: show" box. You could enter in the "show" a NR (for no response), "blanks" will be listed in the Pivot Table as "NR", but this text will not calculate in the count. If you wanted the PT to "look" blank (NOT have the "NR", I found that entering <alt>0001 into the "show" would also work (I'm sure there are others). Using a <space> actually but a zero into the PT, which is why I tried the <alt> sequence.

I use XL97 so your results may vary.

Steve

11. ## Re: Pivot Table Sum (2000/SR1)

Hi there,

The problem with your pivot table seems to be that some of the blank cells on your data sheet are not truly empty. You'll see that some of the blank cells return 0s in the pivot table while others don't. You can fix that by selecting each balnk cell on your data sheet and pressing delete. Alternatively, run the following macro:

Sub ClearBlanks()
For Each Cell In Worksheets("Data").Cells.SpecialCells(xlConstants)
[tab[tabIf Len(Cell.Value) = 0 Then
[tab[tab[tabCell.ClearContents
[tab[tabEnd If
Next Cell
End Sub

Cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•