Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-24-2017, 09:22 PM
jrnisaac1 jrnisaac1 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Apr 2017
Posts: 2
jrnisaac1 is on a distinguished road
Default Used an Array to Add a Median to Pivot Table- Now need Median to also appear on graph

Ok everyone, I have spent some time trying to work this out but I feel that I need to ask for assistance. I have a simple data table, that has a daily time value added for each day. Table includes three critical fields. Date and Time (how long it took for a process to complete).

I created a pivot table , but since you are not able to calculate a median from within the pivot table, I created an array calculated field that displays the calculated median. So in this Pivot table I now have the date, minTime, maxTime, and the foreign Median. I try to create a line graph to plot all three values per month, but only the minTime and maxTime display on graph.

Please assist. I have attached a sample
Attached Files
File Type: xlsx Pivot with Mean.xlsx (21.4 KB, 1 views)
Reply With Quote
  #2  
Old 04-24-2017, 10:19 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,007
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Maybe like shown in Pivot_2
Attached Files
File Type: xlsx Pivot with Mean_2.xlsx (38.8 KB, 3 views)
Reply With Quote
  #3  
Old 04-24-2017, 10:28 PM
jrnisaac1 jrnisaac1 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Apr 2017
Posts: 2
jrnisaac1 is on a distinguished road
Default

I see that you used the Average function within the table. In some cases the average and median might be similar, but with the tight ship our health organization is running, they want to look at the actual median of the set of numbers, in this case the median for all times entered during each month. Let me know if I may not be looking at your sheet correctly.

Thank you
Reply With Quote
  #4  
Old 04-25-2017, 08:45 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,007
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

I have added column E in sheet Data and here calculated the median using a formula similar to your own.

Note that the median in Pivot_2 is exactly 32 while =AVERAGE(Data!C2:C100) is 32,8484848484849.

Please note that the data source of Pivot_2 is Data!A1:E100.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create PIVOT chart using few rows in pivot table Santhosh_84 Excel 1 08-31-2015 06:22 AM
Adding information from a array into a table jcorti Word VBA 4 01-10-2014 10:58 PM
Copying pivot table and graph to a new worksheet artner0112 Excel 0 02-02-2013 07:19 PM
Convert String Array to Integer Array from a User Input? tinfanide Excel Programming 4 12-26-2012 08:56 PM
Excel Not Finding the Median BrazzellMarketing Excel 4 02-17-2012 02:20 PM


All times are GMT -7. The time now is 02:09 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft