 # 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

• 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!

MS Excel MVP 2010-2016

• 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!

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".

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.

• Don't see any attachment.

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

MS Excel MVP 2010-2016

• 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!

MS Excel MVP 2010-2016

• 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!

MS Excel MVP 2010-2016