Calculated Fields in PivotTables...any trick/workaround to get an "average" to work as the aggregator?

TomCon

Active Member
Joined
Mar 31, 2011
Messages
268
Office Version
365
Platform
Windows, Mobile
My understanding of Calculated Fields in PivotTables is that "sum" is used to aggregate a reference to a PivotTable field, and then the formula you define for the field is applied to that sum. I have verified that this behaves as stated.

Does anybody know of a workaround to get a CalculatedField in a PivotTable to use the average? Of course you cannot manually compute that average as SUM/COUNT as you can only use SUM and can't use COUNT, either!

So, lets say Field1 as a calculated field in a PivotTable is defined as:
Field1 = income * 2
where "income" is a field in the PivotTable's data range

If what i want for the result of Field1 is: (AVERAGE of income) * 2 [not: (SUM of income) * 2] ...

anybody know of any tricks or workarounds to get it?

Thanks!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,174
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If you have Power Pivot, it’s simple. If not, you can add a counter column to the data that just returns 1 for each applicable row so that you can sum that to get a count. You can then calculate your average using SUM as required by calculated fields.
 

TomCon

Active Member
Joined
Mar 31, 2011
Messages
268
Office Version
365
Platform
Windows, Mobile
Thanks...that does work. Appreciate it. Always hate to "mess up the sheet" with dummy type columns, would have been nice to hide it all in a CalculatedField..but oh well, limitations do exist. As an editorial, too bad they did not just make CalcuatedFields have all the same aggregators available that PivotTables have, and allow CalculatedField language to have syntax like SUM(field), AVERAGE(field), Max(field), etc. Do appreciate the post, it will serve my purpose here!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,822
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top