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

Row\Col	A​	B​	C​
4​
5​	Row Labels	Average of Today's Line	Average of Implied
6​	Stephen Curry	119.25​	112.66​
7​	Russell Westbrook	115.25​	109.44​
8​	Norris Cole	112.25​	102.67​
9​	Jrue Holiday	112.25​	102.67​
10​	Tony Parker	110.00​	103.73​
11​	Isaiah Thomas	108.25​	104.20​
12​	Marcus Smart	108.25​	104.20​
13​	Ricky Rubio	107.25​	100.19​
14​	Mike Conley	106.75​	98.58​
15​	Damian Lillard	106.00​	101.62​
16​	Shelvin Mack	104.50​	102.81​
17​	Patrick Beverley	104.00​	105.63​
18​	Goran Dragic	103.25​	98.06​
19​	E'Twaun Moore	102.50​	101.63​
20​	Aaron Brooks	102.50​	101.33​
21​	Michael Carter-Williams	102.00​	98.36​
22​	Reggie Jackson	100.50​	100.71​
23​	Archie Goodwin	100.25​	101.39​
24​	Ronnie Price	100.25​	101.45​
25​	Phil Pressey	100.25​	99.50​
26​	Donald Sloan	93.00​	96.72​

<tbody>
</tbody>

Sheet: PaceUp

<tbody>
</tbody>

 

[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.