Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Pivot Tables and Calculated Fields (2003)

    Hi All,

    There was a question about creating a weighted average in a pivot table in another forum. I tried going thru the steps and it works as advertised (see attached workbook). However, it makes use of an extra column and a calculated field based on that column. When I go thru the steps of creating a pivot table and click Finish (as part of the 3rd step), I get the normal pivot table "template" into which to drag my col and row headings. Instead of doing that, I choose the PivotTable drop down on the Pivot Table toolbar, click on Formulas and choose Calculated Field. When I click on Calculated Field and define my calculation for the field, two things happen:
    - I no longer get to choose col and row headings
    - the pivot table just contains the weighted average and I can't seem to expand anything. Although the "statistic" for the pivot table says sum, it's really not a sum as I'd think of it.

    Since I've never used a calculated field in a pivot table, I'm not totally sure of what's going on in this case. Would appreciate an explanation. Is this some kind of shortcut that MS created? Is there any advantage of using a pivot table in this way, since clearly D6/C6 (where col D needed to be added anyway to make the pivot table approach work) would give me the same answer without all the trouble of the pivot table dialog?

    I also noticed that there's a calculated item choice even though it's grayed out. What's the diff between a calculated item vs a calculated field and when would the former be available?

    TIA

    Fred

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

    Re: Pivot Tables and Calculated Fields (2003)

    1) If you are primarily interested in the weighted average, you don't need a pivot table. As you note, the formula =D6/C6 calculates it without using the pivot table. You can even do without the auxiliary column:

    =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

    2) If you want to do more with the pivot table (for what it's worth), click in one of its cells, select Pivot Table | Wizard from the Pivot Table toolbar, then click Layout...

    3) The "Sum of ..." is just the default name for the calculated field. Click in the calculated value and select Pivot Table | Field Settings from the Pivot Table toolbar to change the name (for example to Weighted Average).

    4) Try a Google search for pivot table calculated item.

  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 Tables and Calculated Fields (2003)

    Using the pivot table has the advantage if you have multiple categories in the table.

    The sumproduct (as Hans suggested) works if the data is all one group

    If there is multiple groups (but all in 1 type) you can use the sumif and countif

    If there are multiple groups with multiple types, array formulas can be done.

    The disadvantage of the ways with multiple groups is you must provide each of the groups to base the calculations on. In these cases it may just be easier to use the pivot table to have it extract the various groups. Adding an extra column in the data and the calculated field is easier to setup and maintain than having to extract out all the various combinations required.

    Steve

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Pivot Tables and Calculated Fields (2003)

    Hi Steve,

    Thks. I certainly know what sumprod, array formulas, sumif, countif are all about and could use those.

    Not sure what the multiple categories are about (I know what you mean in terms of multiple categores vs cols/rows in a PT but not sure how that relates to the weighted average). Maybe bcs my example was trivial that Excel gave me a trivial PT and didn't allow me to do any dragging for cols and rows once I defined a calculated field. Are you suggesting I could get multiple weighted averages if my data was more elaborate? Are you also suggesting that I might be able to see more info, maybe even define some rows and cols, under certain conditions of my data and/or the fact that I have a calc'd field? Maybe an example - more on point than the one I posted - would help.

    Still Confused.

    Fred

  5. #5
    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 Tables and Calculated Fields (2003)

    I hope this is self explanatory. If not please ask. I have 3 sheets with the 3 types (it can be extended further and the arrays will get more complex). The pivot table can even change the groupings on the fly, add more segments and it will not really have to be modified (you just need extra column).

    Each one adds more complexity to the same data set so you can see how the numbers come from each to the next...

    Steve

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Pivot Tables and Calculated Fields (2003)

    Hi Steve,

    Thks for the example. I do understand what's going on there, except for one thing: I assume that the calc'd field "Wtd Avg" was defined as Num*Wt/Wt based on past examples. However, I couldn't see how to find this formula once the PT is done. I see where the calc'd field is mentioned but don't see its definition.

    As an aside, I thought it odd that Excel allows an * as part of the name of the calc'd field. Maybe because the set of characters "Num*Wt" is being used in the name for a calc'd field, this is ok. But would Excel allow one to use a calc'd field as part of the formula for another calc'd field? If so, would there be any confusion for Excel seeing the * as part of the name as opposed to thinking you wanted to multiply a field called "Num" by a field called "Wt"?

    Fred

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

    Re: Pivot Tables and Calculated Fields (2003)

    Click in the pivot table.
    Select Pivot Table | Formulas | Calculated Field from the pivot table toolbar.
    Select Wtd Avg from the Name dropdown list.
    You'll see that it is defined as
    [code
    ='Num*Wt'/Wt
    [/code]
    The single quotes around Num*Wt ensure that it is interpreted as a field name, not as a calculation.

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Pivot Tables and Calculated Fields (2003)

    Thanks Hans.

    Looks like this was a case of "killing two birds with one stone" - once I found the formula, I saw the quotes.

    Fred

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables and Calculated Fields (2003)

    A server crash somewhere in the past deleted the attachement workbook. would you have a copy you could repost?

  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 Tables and Calculated Fields (2003)

    Here it is

    Steve
    Attached Files Attached Files

Posting Permissions

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