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

User · Answer

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

User · Answer

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 gt 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

User · Answer

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

User · Answer

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

[excel] Use formula in custom calculated field in Pivot Table

Examples related to excel

Examples related to excel-2010

Examples related to pivot-table