Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    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.

  2. #2
    Plutonium Lounger
    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.

  3. #3
    4 Star Lounger
    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.

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='592747' date='15-Aug-2006 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

  5. #5
    Plutonium Lounger
    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.
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796602' date='06-Oct-2009 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.
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='jepalmer' post='796632' date='06-Oct-2009 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?

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

  9. #9
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='jepalmer' post='796632' date='06-Oct-2009 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))
    Attached Files Attached Files

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='796651' date='06-Oct-2009 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))
    [/quote]


    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •