Use formula in custom calculated field in Pivot Table

Question

In Excel Pivot table report there is possibility for user intervention by inserting "Calculated Field" so that user can further manipulate the report. This seems like best approach compared to using formula on Pivot table data, outside the Pivot table, for many obvious reasons.

"Calculated Field" dialog, looks like this:

and while it's easy to do calculation between available variables (as shown in screenshot) I can't find how to reference range of values for any of available variables.

For example, if for some reason I want to center the data in range A1:A100 I'd use = A1 - AVERAGE(A1:A100) and fill all rows in regular Excel table. But for Pivot table, if I use "Calculated Field" dialog and add new variable with formula: = 'Actual Sales' - AVERAGE('Actual Sales') I get 0 as output.

So my question is how can I reference whole range for 'Actual Sales' variable in "Calculated Field" dialog, so that AVERAGE() will return the average of all targeted cells ?

Answer 1

I'll post this comment as answer, as I'm confident enough that what I asked is not possible.

I) Couple of similar questions trying to do the same, without success:

• Pivot Calculated formula: SUM(Field1)/AVG(Field2)
• Excel Pivot Table Calculated Field

II) This article: Excel Pivot Table Calculated Field for example lists many restrictions of Calculated Field:

• For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.
• Calculated field formulas cannot refer to the pivot table totals or subtotals
• Calculated field formulas cannot refer to worksheet cells by address or by name.
• Sum is the only function available for a calculated field.
• Calculated fields are not available in an OLAP-based pivot table.

III) There is tiny limited possibility to use AVERAGE() and similar function for a range of cells, but that applies only if Pivot table doesn't have grouped cells, which allows listing the cells as items in new group (right to "Fileds" listbox in above screenshot) and then user can calculate AVERAGE(), referencing explicitly every item (cell), from Items listbox, as argument. Maybe it's better explained here: Calculate values in a PivotTable report
For my Pivot table it wasn't applicable because my range wasn't small enough, this option to be sane choice.

Answer 2

Some of it is possible, specifically accessing subtotals:

"In Excel 2010+, you can right-click on the values and select Show Values As –> % of Parent Row Total." (or % of Parent Column Total)

• And make sure the field in question is a number field that can be summed, it does not work for text fields for which only count is normally informative.

Source: http://datapigtechnologies.com/blog/index.php/excel-2010-pivottable-subtotals/

Answer 3

Thank you for planting a seed, Cel! I've been struggling with this for hours, finally got it. I was counting a text field, oops, calculation failed.

Created 2 helper columns in my raw data, each resulting in 1 if condition met, 0 if not. Then pulled each into a pivot column, mine are called, "Inbd" (for Inbound), "Back", where "Back" is a return to sending facility, so in reality the total is one trip, not 2 trips, i.e., back is a subset of inbound and not every inbd has a back (obviously). Trying to calculate in the pivot table so I can sort on the field the rate of back to inbound for each sending facility.

For my calculated field I used: =IFERROR(IF(Pvt_Back>0,Pvt_Back/Pvt_Inbd,0),0) So: if we sent back to sending some number of times greater than 0, divide Back/Inbd to give me a rate; if equal to 0, then 0; if Inbd = 0, then 0 to avoid Div/0 error.

Thanks again!! :)

Answer 4

Pivot table Excel2007- average to exclude zeros

=sum(XX:XX)/count if(XX:XX, ">0")

Invoice USD

Qty Rate(count) Value (sum) 300 0.000 000.000 1000 0.385 385.000

Average Rate Count should Exclude 0.000 rate