+ Reply to Thread
Results 1 to 3 of 3

Calculate medians by season

  1. #1
    Registered User
    Join Date
    11-04-2014
    Location
    Ohio, USA
    MS-Off Ver
    Microsoft Office 365, version 15.0
    Posts
    2

    Calculate medians by season

    I'm working with a larger data set that I've categorized into seasons per year (see Seasons.xlsx ). I need to find a way to calculate the medians of each season in each year, and I'd rather not do this manually for each season.
    I've used the following equation (with accompanying source) to find monthly medians, but I'm unsure how to use this on seasonal categories:
    =MEDIAN(IF(DATE(YEAR(AllDates),MONTH(AllDates),1)=E2,AllData)) from http://excel.tips.net/T003086_Derivi...an_Values.html

    I figure there's a way to incorporate the seasonal categorizations I've created, but it might be easier just to group months within the equation, though I'm not sure how to do this either.

    Thanks for any help.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Calculate medians by season

    I have assumed that December is Winter of the following year: Dec 2002 is winter 2003.

    For the year assignment:

    =YEAR(A2)+IF(MONTH(A2)=12,1,0)

    For the season assignment:

    =CHOOSE(INT(MOD(MONTH(A2),12)/3)+1,"Winter","Spring","Summer","Autumn")

    The other stuff was unnecessary.


    Seasons 2.xlsx
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    11-04-2014
    Location
    Ohio, USA
    MS-Off Ver
    Microsoft Office 365, version 15.0
    Posts
    2

    Re: Calculate medians by season

    This was a much simpler solution than what I had in mind. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to calculate medians using macro?
    By jun22 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2014, 03:11 PM
  2. [SOLVED] Medians for Incremental Ranges
    By Tams80 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-29-2014, 10:45 AM
  3. Medians with multiple ifs.
    By FoxyDread in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2013, 01:30 PM
  4. Creating a macro to calculate medians for every 18-row range
    By niuren in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-13-2011, 07:41 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1