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

TomCon

Active Member
Joined
Mar 31, 2011
Messages
268
Office Version
365
Platform
Windows, Mobile
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!
 

Some videos you may like

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.

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top