# Can I get a PivotTable calculated field that is the difference of two averages?

#### TomCon

##### Active Member
Lets say i have these PovotTable fields. FieldRow, FieldData1 and FieldData2. I put FieldRow in the Row area of the PivotTable, and so data fields will be summarized for each item of FieldRow. I put FieldData1 and FieldData2 in the Data area, and set them to aggregate by Average. I would like the difference between the average of FieldData1 and FieldData2 (separately for each item in FieldRow). Of course this can be done outside of the PivotTable, but it is hard to maintain since the table may change in size.

I tried creating a CalculatedField with definition "FieldData1-FieldData2". Putting this field in the PivotTable DataField area, the numeric results do not change whatever the aggregation function is set to, Sum, Average, Max, etc., they all give the same numeric result. This is rather confusing. Verifying the numbers, what i found is that the result given in the CalculatedField is in fact the difference in the SUM(FieldData1) - SUM(FieldData2).

So, i went back into the CalculatedField designer and defined the CalculatedField as "AVERAGE(FieldData1)-AVERAGE(FieldData2)". This CalculatedField gives exactly the same result as the previous version (without the AVERAGE function in the definition) - in other words it gives the difference of the SUMS, not the difference of the AVERAGEs. And, this new calculated field also has the same behavior in the PivotTable, that whatever its PivotTable aggregation function is set to, MIN, MAX, SUM, AVERAGE...does not effect the result value.

So, the question is, is there a way to create a PivotTable CalculatedField that is the difference between the AVERAGEs of two other data fields? So far i cannot find a way to accomplish this.

Thanks!

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

1,101,988
Messages
5,484,056
Members
407,426
Latest member
Owen Chia

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...