Calculated field in pivot table

[Reboshua]

I am trying to figure out how to do a calculation on two fields in a pivot table. The struggle I'm having is that these two fields are already calculated as averages. I want to create a calculated field in column D that will express the value in B as a percentage of the value in C. So, in row 6 for Stephen Curry, the value in D would be 5.84% (119.25/112.66).

Thoughts?

Excel 2010
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]_Row\^Col[/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD="bgcolor: #DCE6F1"]Row Labels[/TD]
[TD="bgcolor: #DCE6F1"]Average of Today's Line[/TD]
[TD="bgcolor: #DCE6F1"]Average of Implied[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6​

[/TD]
[TD]Stephen Curry[/TD]
[TD]

119.25​

[/TD]
[TD]

112.66​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7​

[/TD]
[TD]Russell Westbrook[/TD]
[TD]

115.25​

[/TD]
[TD]

109.44​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

8​

[/TD]
[TD]Norris Cole[/TD]
[TD]

112.25​

[/TD]
[TD]

102.67​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

9​

[/TD]
[TD]Jrue Holiday[/TD]
[TD]

112.25​

[/TD]
[TD]

102.67​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

10​

[/TD]
[TD]Tony Parker[/TD]
[TD]

110.00​

[/TD]
[TD]

103.73​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

11​

[/TD]
[TD]Isaiah Thomas[/TD]
[TD]

108.25​

[/TD]
[TD]

104.20​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

12​

[/TD]
[TD]Marcus Smart[/TD]
[TD]

108.25​

[/TD]
[TD]

104.20​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

13​

[/TD]
[TD]Ricky Rubio[/TD]
[TD]

107.25​

[/TD]
[TD]

100.19​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

14​

[/TD]
[TD]Mike Conley[/TD]
[TD]

106.75​

[/TD]
[TD]

98.58​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

15​

[/TD]
[TD]Damian Lillard[/TD]
[TD]

106.00​

[/TD]
[TD]

101.62​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

16​

[/TD]
[TD]Shelvin Mack[/TD]
[TD]

104.50​

[/TD]
[TD]

102.81​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

17​

[/TD]
[TD]Patrick Beverley[/TD]
[TD]

104.00​

[/TD]
[TD]

105.63​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

18​

[/TD]
[TD]Goran Dragic[/TD]
[TD]

103.25​

[/TD]
[TD]

98.06​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

19​

[/TD]
[TD]E'Twaun Moore[/TD]
[TD]

102.50​

[/TD]
[TD]

101.63​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

20​

[/TD]
[TD]Aaron Brooks[/TD]
[TD]

102.50​

[/TD]
[TD]

101.33​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

21​

[/TD]
[TD]Michael Carter-Williams[/TD]
[TD]

102.00​

[/TD]
[TD]

98.36​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

22​

[/TD]
[TD]Reggie Jackson[/TD]
[TD]

100.50​

[/TD]
[TD]

100.71​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

23​

[/TD]
[TD]Archie Goodwin[/TD]
[TD]

100.25​

[/TD]
[TD]

101.39​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

24​

[/TD]
[TD]Ronnie Price[/TD]
[TD]

100.25​

[/TD]
[TD]

101.45​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

25​

[/TD]
[TD]Phil Pressey[/TD]
[TD]

100.25​

[/TD]
[TD]

99.50​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

26​

[/TD]
[TD]Donald Sloan[/TD]
[TD]

93.00​

[/TD]
[TD]

96.72​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: PaceUp[/TD]
[/TR]
</tbody>[/TABLE]

 

[billbrunt]

Hi -

Assuming the pivot table above creates formulas such as those shown below:

The formula shown as text in E2 is the formula in D2. If this were just copied from D2 to D3, Excel would copy this as the same formula.

If the fourth argument in the GETPIVOT function is changed from "bill brunt" as literal value to A3 as a cell reference, the revised formula using cell references will copy down and produce the desired results perhaps?

- Bill Brunt

 

[Reboshua]

Bill - thanks for replying. I am hoping to be able to do a calculation as part of the pivot table itself, hopefully as a calculated field, so that the table can be sorted properly by the results in column D. Your approach will work, especially when I change the set names to cell references, but it doesn't solve my problem unfortunately. Thanks though.

 

[billbrunt]

Hi -

Thanks for letting me know. Is a macro an option for this? One click run in the quick access tool bar?

- Bill

 

[Jerry Sullivan]

Bill - thanks for replying. I am hoping to be able to do a calculation as part of the pivot table itself, hopefully as a calculated field, so that the table can be sorted properly by the results in column D. Your approach will work, especially when I change the set names to cell references, but it doesn't solve my problem unfortunately. Thanks though.

Reboshua, The fields "Average of Today's Line" and "Average of Implied" - are they Calculated Fields or are you just using the "Summarize by Average" option on the data? If they are Calculated Fields then you can make a Calculated Field that uses those two formulas.

If it's just summarized by Average- are there the same number of records for each PivotItem's, "Today's Line" and "Implied"? As an example, if Stephen Curry has an average of "119.25" for 7 "Today's Line" records, and if that means that "112.66" is also the average of 7 records (not more or less than 7), then your Calculated field in Column D would be 1-(Sum of Today's Line/Sum of Implied).

If you setup doesn't allow you either of those options, you could consider VBA as Bill Brunt suggested, or an SQL query.