Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    Thanks,
    Caroline in lala-land

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    Attached Images Attached Images

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table Sum (2000/SR1)

    This doesn't answer your question, but just use sumif and countif to get your average:

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

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

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    Attached Files Attached Files
    Thanks,
    Caroline in lala-land

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Thanks,
    Caroline in lala-land

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    Thanks,
    Caroline in lala-land

  8. #8
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    Thanks,
    Caroline in lala-land

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table Sum (2000/SR1)

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

    Steve

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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