Results 1 to 10 of 10
Thread: median monthly value (2003)

20060815, 15:14 #1
 Join Date
 May 2002
 Posts
 410
 Thanks
 1
 Thanked 0 Times in 0 Posts
median monthly value (2003)
I need to determine the median value for each month for about 60 years of daily data. Any suggestions as a way to go about this?
Thanks.

20060815, 15:23 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: median monthly value (2003)
See the attached workbook for a method to calculate the median values using a table of months and array formulas.
If this is too slow, an alternative could be to use VBA code.

20060828, 19:16 #3
 Join Date
 May 2002
 Posts
 410
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: median monthly value (2003)
Sorry for the brain fade in forgetting to reply.. The advice was spot on!
Thanks.

20091006, 10:05 #4
 Join Date
 May 2002
 Posts
 410
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='592747' date='15Aug2006 19:23']See the attached workbook for a method to calculate the median values using a table of months and array formulas.
If this is too slow, an alternative could be to use VBA code.[/quote]
Hans
I had a computer crash and can't find the example in the backup. Could you repost?
Embarrassedly yours
John

20091006, 10:17 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
I don't have the original workbook any more but here is a new one.

20091006, 13:20 #6
 Join Date
 May 2002
 Posts
 410
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='796602' date='06Oct2009 15:17']I don't have the original workbook any more but here is a new one.[/quote]
Thanks.
Now I have another question. Can this be modified so that a date range is used rather than a month or year? I work with hydrologic data which is analyzed by 'water year' which runs from October 1 to September 30; e.g., water year 2008 ran from 10/1/2007 until 9/30/2008, water year 2009 ran from 10/1/2008 until 9/30/2009, etc. Example data set is attached.
Thanks.

20091006, 13:34 #7
 Join Date
 May 2002
 Posts
 410
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='jepalmer' post='796632' date='06Oct2009 18:20']Thanks.
Now I have another question. Can this be modified so that a date range is used rather than a month or year? I work with hydrologic data which is analyzed by 'water year' which runs from October 1 to September 30; e.g., water year 2008 ran from 10/1/2007 until 9/30/2008, water year 2009 ran from 10/1/2008 until 9/30/2009, etc. Example data set is attached.
Thanks.[/quote]
also, does the newest windows excel do medians in pivot tables?

20091006, 14:36 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
[quote name='jepalmer' post='796635' date='06Oct2009 19:34']also, does the newest windows excel do medians in pivot tables?[/quote]
Not in Excel 2007.

20091006, 15:11 #9
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='jepalmer' post='796632' date='06Oct2009 13:20']Thanks.
Now I have another question. Can this be modified so that a date range is used rather than a month or year? I work with hydrologic data which is analyzed by 'water year' which runs from October 1 to September 30; e.g., water year 2008 ran from 10/1/2007 until 9/30/2008, water year 2009 ran from 10/1/2008 until 9/30/2009, etc. Example data set is attached.
Thanks.[/quote]
If the year is in cell D2 (this too is an array formula):
Code:=MEDIAN(IF(YEAR(DATE(YEAR($A$2:$A$3288),(3+MONTH($A$2:$A$3288)),1))=D2,$B$2:$B$3288))

20091006, 16:35 #10
 Join Date
 May 2002
 Posts
 410
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='mbarron' post='796651' date='06Oct2009 19:11']If the year is in cell D2 (this too is an array formula):
Code:=MEDIAN(IF(YEAR(DATE(YEAR($A$2:$A$3288),(3+MONTH($A$2:$A$3288)),1))=D2,$B$2:$B$3288))
Thanks!