Microsoft Office Forums Used an Array to Add a Median to Pivot Table- Now need Median to also appear on graph
 Register FAQ Search Today's Posts Mark Forums Read

#1
04-24-2017, 09:22 PM
 jrnisaac1 Windows 7 64bit Office 2007 Novice Join Date: Apr 2017 Posts: 2
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
 Pivot with Mean.xlsx (21.4 KB, 1 views)
#2
04-24-2017, 10:19 PM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,007

Maybe like shown in Pivot_2
Attached Files
 Pivot with Mean_2.xlsx (38.8 KB, 3 views)
#3
04-24-2017, 10:28 PM
 jrnisaac1 Windows 7 64bit Office 2007 Novice Join Date: Apr 2017 Posts: 2

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
#4
04-25-2017, 08:45 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,007

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.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Santhosh_84 Excel 1 08-31-2015 06:22 AM jcorti Word VBA 4 01-10-2014 10:58 PM artner0112 Excel 0 02-02-2013 07:19 PM tinfanide Excel Programming 4 12-26-2012 08:56 PM BrazzellMarketing Excel 4 02-17-2012 02:20 PM

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

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top