Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot calculation (2003 (SP2))

    This is a first for me; trying to add a calculated field into a pivot table.

    I have 2 basic fields; both count, & I want to add a percentage calculation of one from the other. Trouble is, I can't get a formula that produces anything like expected.

    Anyone tell me what I should be doing please.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot calculation (2003 (SP2))

    It might help if you posted a small sample workbook.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot calculation (2003 (SP2))

    Oops, that would help.

    Here's a very basic example
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot calculation (2003 (SP2))

    A calculated field performs calculations on the source data, not on the aggregated data. Your calculated field divides the TestY column by the Test PC No column in the source data, resulting in meaningless values, then adds those together.
    As far as I know (but I could well be wrong), you cannot do what you want in a pivot table. You could place formulas next to the pivot table (see attached version), but of course that wouldn't work if you want to change the layout of the pivot table.
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot calculation (2003 (SP2))

    Bum. That's exactly what I was hoping to do.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pivot calculation (2003 (SP2))

    1. Add a column to your data which just returns 1 for each PC Number. (let's call the column 'PC Count')
    2. Refresh your data to include the new column.
    3. Amend your calculated field to be <code>=TestY/'PC Count'</code>
    4. Format the new data field (needs to be Sum, not Count) as a percentage.

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot calculation (2003 (SP2))

    HAHA... Hans is human after all!

    Hans, I honestly thought that if you did not know, then that was it.

    Rory, I bow down to your knowledge [img]/forums/images/smilies/smile.gif[/img]


    Thanks to both of you.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot calculation (2003 (SP2))

    I'm just a guy sitting behind a PC... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pivot calculation (2003 (SP2))

    Don't be fooled - he just likes to let the rest of us have a go from time to time! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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