Results 1 to 10 of 10

20060402, 22:19 #1
 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

20060402, 22:55 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20060403, 10:25 #3
 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

20060403, 21:26 #4
 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

20060403, 22:09 #5
 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

20060403, 22:59 #6
 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

20060404, 05:09 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20060404, 11:47 #8
 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

20080404, 20:06 #9
 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?

20080405, 11:30 #10
 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