Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Erica
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Bernie Deitrick
 
Posts: n/a
Default Add quartiles and mode functions to pivots tables in Excel!

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" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 10:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"