Can I create a custom summary field in Excel 2007 pivot table?

Question

Is there anyways to create a custom "summarize value field by" function to be used in an XL 2007 pivot table. The standard Sum, Count, Average, Max, etc. are not sufficient for me. I would accept a solution in VBA, or a solution that requires an XLA or XLL.

please note that I'm NOT asking about Calculated Field or Calculated Item. Neither serve my purpose - I need to summarize the underlying data with functions such as Median, 1st Quartile, etc.

I don't think so, but I would like to know the answer for sure. — jtolle, Mar 29 '11 at 18:35
Agree - I don't think there's a way to rig a call back to a custom function - so you need to either (i) add data cols w interim calcs so that you can get the result you want via the built-in aggregation funcs, or (ii) write VBA code to do the aggregration & totalling brute force. It's a shame there isn't a better way - even simple things like weighted averages are a pain w pivot tables. — tpascale, Mar 29 '11 at 19:06

score 1 · Accepted Answer · answered Mar 30 '11 at 19:16

1

No this is not possible with Excel pivot tables, even in Excel 2010.
Using the PowerPivot Excel 2010 addin I believe its possible to effectively create this kind of function using DAX, and MDX has more built-in functions such as MEDIAN.

answered Mar 30 '11 at 19:16

Charles Williams

23,121
5
38
38

Thanks for a definitive answer. – jtolle Mar 30 '11 at 19:44
@subsetsum if you have a comment on this answer, post it as a comment. Substantive edits that should be comments will be rejected. – user207421 Nov 21 '11 at 01:31

score 0 · Answer 2 · answered Mar 29 '11 at 23:13

0

Unless I hear otherwise, I'm going to close out this questions with the following answer: No, this cannot be done.

answered Mar 29 '11 at 23:13

Suraj

35,905
47
139
250

Can I create a custom summary field in Excel 2007 pivot table?

2 Answers2