Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Unanswered: Calculated Fields in Pivot Table

    I'm trying to get a calculated field in a Pivot Table. But not a regular one... Can you look in the example below and let me know if it can be done (and how it can be done)?

    I have an Excel database with Toy Boxes. Here is the information about each Toy: Box Number, Box Location, Number of Toys in box, Value of Toys for a specific Toy Type, value of Box.

    In the Pivot Table I have the Locations and Boxes in the Rows area, the Type in the Columns area and the value in the data area.

    I attached the excel spread sheet (in a zipped file) to this post.

    What I want is to have a calculated field for:
    1. Number of Toys / Total Value
    2. Total Value / Number of Boxes

    Can it be done?
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy and welcome to the board.

    Pivottables can be powerful, and frustrating. There are a couple of ways to approach this, but not sure what you still need. On the attachment are two approaches.

    PT1 shows what happens when you add a second copy of the same field (Value) but change it to be a percent. in this case, right click on any cell with the second value and choose Options, and then in the middle dropdown choose "% of Total". Then choose number format to make it percent.

    PT2, inserts a calculated field (Pivottable toolbar, Pivottable > Formulas > Calculated Field. In the top define the name, then in the next dialog box you can put the formula. Use the fields listed in the lower portion to make the formula.

    This probably is not what you want, but it might direct you to solving your problem.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2005
    Posts
    3

    This wasn't what I was looking for...

    The 2 examples you gave me are not what I'm looking for. I tried using the same mechanism for what I want but I can't find a way to do it.

    Any other ideas?

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    OK thought I'd take a try at this. First off mixing Box data and Location data in the same pivot table results in an overly complex table that doesn't give clear information for location or box contents. I'd suggest using 2 pivot tables one to show and calculate the Location data and one to show and calculate the Box data. I've attached an example zip file see the 2 pivot tables below your original sample.

    The 'Count of Toys in box' Column in your data is not needed and not used in the pivot table as the count of toys can be calculated from the count of Box IDs. Each Toy has a box identifier.

    Also be aware of the calculation function. You want to * count * boxes (box ids) and * sum * toy values. When you set up the pivot table it will be necessary to select the data field and set the calc function for each of the fields Boxes and Value.

    The Formulas can not be made relative to the cells in the Pivot table. At least I can't see a way of making them relative. This means each time you add data or recreate the pivot table you'll need to set each formula for every row calculation. You can however refer to the cells in your Pivot table. (not sure if you realized this) So the formulas will be like this:

    Code:
    Total Value / # of Boxes (for each location)
    =GETPIVOTDATA("Sum of Value",$A$30,"Location","US")/GETPIVOTDATA("Count of Box",$A$30,"Location","US")
    
    # of Toys / Total Value
    =GETPIVOTDATA("Count of Box",$A$40,"Box",1)/GETPIVOTDATA("Sum of Value",$A$40,"Box",1)
    Attached Files Attached Files
    ~

    Bill

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    These kinds of examples seem to justify the position of Bill Jelen [MrExcel] and Tracy Syrstad in VBA and Macros for MS Excel, namely that Pivot tables are powerful but frustrating, and much easier to do in VBA than in Excel. At work I have experimented with VBA for Pivot tables and it seems to be true.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Oct 2009
    Posts
    8
    This kind of problem can usually be solved with some judicial use of source data editing. Please see the attached, where I have added two fields to the source data table. This achieves the desired result.

    PivotTableQuestion3.zip

Posting Permissions

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