Finding the Median for certain dates from a list of data

  • Hello - I have a worksheet with lots of raw data which includes dates. I need to find the median for each month of the fiscal year from the worksheet of raw data.


    For example:

    October Median = ?

    November Median = ?

    December Median = ?


    Etc...


    Any help will be appreciated.

    David

  • You'll have to use an array formula (confirmed with CTRL+SHIFT+ENTER, not just ENTER).


    e.g.


    =MEDIAN(IF(MONTH($A$2:$A$100)=10,$B$2:$B$100))


    where A2:A100 contain dates (formula above looks for 10th month. i.e. October), and B2:B100 contain values to find median of

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Great thanks! So essentially I'm trying to get the Median for each month of the year for different thresholds. For example, column F contains dates and column P contains the "length of stay" which counts the # of days accumulated in care (i.e. 30 days, 45 days, 62 days, etc.) - the number of days vary. So I need to come up with a formula that will give me the Median for <30 days, 30-60 days, 61-90 days, and >90 days for each month of the year.


    I bet that might be a pretty complicated formula huh?

  • You can add more conditions to the formula...


    =MEDIAN(IF(MONTH($F$2:$F$100)=10,IF($P$2:$P$100<30,$P$2:$P$100)))


    =MEDIAN(IF(MONTH($F$2:$F$100)=10,IF($P$2:$P$100>=30,IF($P$2:$P$100<60,$P$2:$P$100))))


    etc.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Yea not sure why it's not working. When I confirm it with Ctrl+Shift+Enter it returns "N/A".

    Attached is the spreadsheet.


    I'm getting the data from worksheet "UAC" - column F has the dates and column P has the "length of care" of which I need the median for each month. In worksheet "LOS Totals" is where I'm inputting the formula for each month, beginning in cell B15 through M15.


    I sure appreciate your help...I am breaking my head over this:\