You can more or less include non-supported aggregate functions in a pivot
table by calculating the aggregates in advance and including them in columns
with your source data, then in the pivot table, group your pivot the same
way the aggregates were calculated. To include those values unchanged in
your pivot, use the Min, Max, or Average functions for those fields in the
pivot table. This will include the values unchanged at the detail level in
your pivot table. Subtotal and grand total lines in the pivot table will
however not be the median for the larger group of data, but instead be the
average, minimum, or maximum of the medians you calculated.
Another alternative would be to not use a pivot table at all, but emulate
the functionality of a pivot table strictly using worksheet formulas and/or
macros. It can be done, and depending on your specific functionality
requirements can be relatively simple or relatively fancy/complex.
If the first option might suffice, take this example. For readibaility, I
assume you are using an Excel 2010 Table to store your data, since we can
refer to fields by name in the formula that way, and the range automatically
adjusts with the addition of new data to the table:
Assume the following table fields:
Name, Type, Amount, Median Amount
Say you want the Median Amount per Type.
In the Median Amount field, use the following formula:
{=MEDIAN(IF([Type]=[@Type],[Amount]))}
(Don't enter the curly braces, they are used to indicate that this is an
array/CSE formula and must be confirmed by hitting Ctrl-Shift-Enter after
editing/entering the formula).
If you are using an Excel version prior to 2010 or don't want your data as a
table for some reason, just modify the formula replacing field names [Type]
and [Amount] with the entire column data range for those fields (such as
$B$2:$B$100 and $C$2:$C$100). Modify the field name [@Type] to a reference
to the current column's single cell in that field with a relative reference
to the row (such as $B2).
For the second, fancy method, if you know all the groups in advance, just
design your "pivot table" by hand, and then for your calculations, use
formulas such as the above array formula pointing back to your source table
fields to calculate the summary values.
If you don't know the groups in advance, you can use some fancy formulas to
determine what they are, such as these. For unique values in [Type] in a
table called "Table1" on "Sheet1" in column B, you can put these formulas on
any sheet:
In Cell A1, put "Type" or whatever header you like.
In Cell A2, to grab the first value, put
=Sheet1!B2
In Cell A3, put the following array/CSE formula (with ctrl-shift-enter)
{=INDEX(Table1[Type],MATCH(0,COUNTIF(A$2:A2,Table1[Type]),0))}
Copy it down for the maximum number of unique items. This
pretty-minimal-for-the-task formula will show one unique value per row, but
after all the unique values it will show #N/A errors, the values won't be
sorted, and blanks in the source data will appear as zeroes.
To get rid of the #N/A errors at the end, you can wrap the formula in
IFERROR (for Excel 2007+) or IF(ISERROR(formula),"",formula) (for older
Excels):
{=IFERROR(INDEX(Table1[Type],MATCH(0,COUNTIF(A$2:A2,Table1[Type]),0)),"")}
For a version that will provide a sorted list:
<http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-s
orted-list-extracted-from-a-column/> Create a unique distinct alphabetically
sorted list, extracted from a column in excel
For a version that will sort the list and skip blanks:
<http://www.get-digital-help.com/2010/07/11/create-a-unique-distinct-sorted-
list-containing-both-numbers-text-removing-blanks-in-excel/> Create a unique
distinct sorted list containing both numbers text removing blanks in excel
For a version that will sort the list, skip blanks, and operate on a
two-dimensional range (Excel 2007+ only):
<http://www.get-digital-help.com/2011/07/28/excel-20072010-array-formula-fil
ter-unique-distinct-values-sorted-and-blanks-removed/> Excel 2007/2010 array
formula: Filter unique distinct values, sorted and blanks removed
Some added work might be needed to get the exact layout/results you want
with this "fancy" approach.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Atul vishwakarma
Sent: Thursday, December 15, 2011 6:21 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Median in Pivot Table
Hi Noorain,
Thanks for your response but I am disappoint to know that we cannot make
user define function within Pivot
--
Atul
On Thu, Dec 15, 2011 at 11:05 AM, NOORAIN ANSARI <noorain.ans...@gmail.com>
wrote:
Dear Atul,
Please see below link, hope it will help to u.
http://www.digdb.com/excel_add_ins/median_pivot_table_roll_up/
--
Thanks & regards,
Noorain Ansari
<http://excelmacroworld.blogspot.com/> http://excelmacroworld.blogspot.com/
<http://noorain-ansari.blogspot.com/> http://noorain-ansari.blogspot.com/
On Thu, Dec 15, 2011 at 12:52 AM, Atul <atulkuma...@sify.com> wrote:
Hi Experts,
Is there any possible way that I can build Median formula inside the
pivot table like average, sum count etc.
Thanks!
Regards,
Atul
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
Thanks & regards,
Noorain Ansari
<http://excelmacroworld.blogspot.com/> http://excelmacroworld.blogspot.com/
<http://noorain-ansari.blogspot.com/> http://noorain-ansari.blogspot.com/
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
