Solved

Average AND Median in Pivot Table

Posted on 2001-08-01
8
2,510 Views
Last Modified: 2007-12-19
I want to create a pivot table and generate both the average and the median of the data values -- easy enough for average/count, etc. but median isn't one of the options in the interface for a pivot table.....any ideas?  THANK YOU!
0
Comment
Question by:lorena2
  • 3
  • 3
  • 2
8 Comments
 
LVL 2

Expert Comment

by:sergeTD
ID: 6342401
Hi lorena2,
I suggest you an indirect way to get median values which are not included in Excel pivot tables:
Let say you have data in the first sheet of the workbook.
First,you have to create a MAX value pivot table in the 2d
sheet.
Second, you do the same but with MIN value in the 3d sheet
and you have to copy values(Paste Special)from the 3d to the 4th sheet
Then, in the 4th sheet you write the following formula in
cell B2:
=(+Sheet2!B2+Sheet3!B2)/2
And you copy B2 in others cells of the Pivot Table.
So, you'll get exactly what you are expecting.
Hope this help,
Serge TD
0
 
LVL 2

Expert Comment

by:sergeTD
ID: 6357089
Hi lorena2,
After 6 days, I'm afraid you still have not take a look upon my comment.
So,I upgrage my solution as an proposed answer.
I hope you 'll agree soon.
-----------------------------------------------------------
I suggest you an indirect way to get median values which are not included in Excel pivot tables:
Let say you have data in the first sheet of the workbook.
First,you have to create a MAX value pivot table in the 2d
sheet.
Second, you do the same but with MIN value in the 3d sheet
and you have to copy values(Paste Special)from the 3d to the 4th sheet
Then, in the 4th sheet you write the following formula in
cell B2:
=(+Sheet2!B2+Sheet3!B2)/2
And you copy B2 in others cells of the Pivot Table.
So, you'll get exactly what you are expecting.
Serge TD
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6373091
Hi, lorena2.

#Questions
1. Did sergeTD's suggestion help you?
If it did, then you should ignore mine and accept that suggestion as the answer.  Otherwise, read on.

#Try This
There is an alternative - although it involves changing the pivotTable.  You should probably ignore this suggestion (and use sergeTD's suggestion instead) if you have a lot of columns, special formatting, or direct cell-referencing in your pivotTable.

a. In the pivotTable wizard, add a max item and a min item in the data section.  Make sure that they are named "Max of <data>" and "Min of <data>".
b. Click Finish.
c. Drag and drop the Data "button" so that the sum,max,and min titles are horizontal.
d. On the actual pivotTable, you can hide the max and min columns.
e. To get the median, use the following formula.

=( GetPivotData(<cell>,"Max of <data>")+GetPivotData(<cell>,"Min of <data>") ) / 2

where:
<cell> is _any_ cell within the pivotTable
<data> is the name of the data field

#Comments
Please return to this question and to the one at (http://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20162457).

Bye. -e2
0
 
LVL 2

Expert Comment

by:sergeTD
ID: 6387138
IS THERE ANYBODY OUT THERE???

lorena2,

We don't have any news from you since more than 2 weeks, since you've asked your question to experts!
Now, the minimum you must to do is looking to the answer that experts have tried to give you!

Are satisfied by my answer? If no, you reject it.
If yes, you accept it.

That is no more complicated.
Serge TD
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:lorena2
ID: 6405968
Sorry sergeTD, I was not ignoring you, I was on vacation.  I appreciate the input.  Your proposed solution gives me the median of two numbers ( a MIN and a MAX ) number vs. the median of several hundred numbers.
0
 

Author Comment

by:lorena2
ID: 6405981
The median of a set of data is the middlemost number in the set.......that set is not just the min and max values but several hundred numbers......  Thanks.  
0
 
LVL 7

Accepted Solution

by:
q2eddie earned 25 total points
ID: 6410471
Hi, lorena2.

I have a working sample project that can determine the median using a pivotTable available at the following link:

briefcase.yahoo.com/q2eddie

(Go to the public\experts-exchange folder and find the file named "PivotTable-Median".  Right-click
and "save target as".)

This is a sample spreadsheet.  Getting the median is pretty rough and cannot be done within the pivotTable itself.  Also, there might be an alternative to the index() function tat I'm using on the pivotTable page - I just can't think of it right now.

Bye. -e2
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6422167
Hi, ppl.

(Just in case, Yahoo ever decides to close my briefcase account...)
The solution is ..
1. include a "row number" column in the dataset
2. Sort the dataset by identifier and by data
3. In addition to the "sum of data" column, have the pivotTable create columns for min of "row" (col C), max of "row" (col D), and count of "row" (col E).
4. Out to the side of the pivotTable, put in the following formulas:

(assume row 3 is the first row of the pivotTable's data)
in cell F#, =FLOOR(C3+E3/2,1)
in cell G#, =CEILING(C3+E3/2,1)

(assume col B on sheet1 is where the data resides)
in cell H#, =INDEX('Sheet1'!$B$1:'Sheet1'!$B$65536,F3,1)
in cell I#, =INDEX('Sheet1'!$B$1:'Sheet1'!$B$65536,G3,1)

(this is the median)
in cell J#, =(H3+I3)/2

#Add'l Comment
(I just found the indirect function.)
You can substitute the following for the above cell formulas:

in cell F#, ="'Sheet1'!B"&FLOOR(C3+E3/2,1)
in cell G#, ="'Sheet1'!B"&CEILING(C3+E3/2,1)

in cell H#, =INDIRECT(F3)
in cell I#, =INDIRECT(G3)

in cell J#, =(H3+I3)/2

Bye. -e2
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel add date to text 3 57
Excel formula 5 70
Excel 2013 Drop down help with data 3 58
Recover lost Hyperlink destination/target in OneNote 3 29
Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now