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!

    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!

    I entered this formula but it doesn't seem to be working. I gotta be doing something wrong.


    If I sent you the spreadsheet i'm working on could you take a look at it and see what am I doing wrong?

    Did you confirm it with CTRL+SHIFT+ENTER?


    If still can't get it then attach it to this thread

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

    You also have to adjust the ranges to suit your data

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

    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:\

    Don't see any attachment.

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

    I'm having the worst luck haha....when I try uploading the file it gives me error message "The parameter "classname" is missing or invalid".Do you have an email address I can email it to?

    We are not supposed to work with email outside forum. Try saving ad .XLSX and try using attachment tool

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

    Check your P range. It needs to be consistent with the F range.

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login