Add quartiles and mode functions to pivots tables in Excel!

If anyone knows of a custom function to calculate quartiles in the pivot
table feature in Excel, you'd improve my life! What about the mode function?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Erica,

Let's say that your orignal data table (the pivot table source) is on Sheet1, with Categories in
column A and Values in column B - let's say, 1000 rows of data.

Make the pivot table, with Categories as the row field, and get your Max, Min, Average, and whatever
else you want that is available using the standard pivot table functionality. Then for the
quartiles, you need to do calculations off of a values copy of the table. Copy your pivot table,
and paste values on a new blank sheet. With the values table still selected, choose Edit / Go To...
Special Blanks OK, type =, press the up arrow key once, and press Ctrl-Enter. Then reselect the
entire table, copy, and paste special to convert to values. This will create a table without any
blanks, making it easy to do the formulas.

In the next column over from your pivot table, Array enter (enter using Ctrl-Shift-Enter) the
following formula (this is for Quartiles, but can also be done for MOD, etc...)

=QUARTILE(IF(Sheet1!$A$1:$A$1000=$A5,Sheet1!$B$1:$B$1000),1)

This assumes that the first Category value of the pivot table is in cell A5 of Sheet2.

If you have more that one row field in your pivot table, you can use (also array entered)

=QUARTILE(IF((Sheet1!$A$1:$A$1000=$A5)*(Sheet1!$B$1:$B$1000=$B5),Sheet1!$C$1:$C$1000),1)

Then copy that down to match the categories in your values table.

HTH,
Bernie
MS Excel MVP


"Erica" <[email protected]> wrote in message
news:[email protected]...
> If anyone knows of a custom function to calculate quartiles in the pivot
> table feature in Excel, you'd improve my life! What about the mode function?
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...lic.excel.misc