Median IF formula query please

[OrthopodAcademic]

Hi
I have a large data set looking at patient outcomes (length of stay in hospital) and want to break it down by month.
I am trying to calculate the median of a column (column I - the number of days in hospital) based on the text in a different column (column D - month e.g. Feb-24, Jan-24, Dec-23 etc)
I have tried to use "=MEDIAN(IF(D:D="Feb-24", I:I))" but I just get a #NUM error!
Can anyone please tell me what I am doing wrong?
Thanks in advance!

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Glenn Kennedy]

If column D contains real dates, it won't work 'cos "feb-24" is text that looks like a date. NEVER use whole column refs with this type of formula as Excel will (slowly) calculate every one of the 1,000,000 rows it can take.

[OrthopodAcademic]

Thank you very much Glenn - the column D does not contain real dates but does contain "Feb-24" or "Jan-24" as text

Does that make a difference? I'm a bit of an Excel amateur...

[Glenn Kennedy]

OK. So post a SMALL sample sheet (anonymised) with 10-20 rows of data

[Glenn Kennedy]

I would probably use AGGREGATE:

=AGGREGATE(16,6,I1:I10/(D1:D10="Feb-24"),0.5)

BUT... I bet you that the column D data ARE dates... as your formula DOES work!!

[OrthopodAcademic]

Ok - please see a sample attached

I have just copied and pasted a few lines from the master spreadsheet... I am trying to find a simple formula to fill in the boxes highlighted in yellow

Thank you so much for your help

[Glenn Kennedy]

The dates in Col D ARE real dates. The value in col I are ALSO real dates. Formatting them to LOOK lile MMM-YY does not change the fact that they ARE dates.

See filer. I changed BOTH sets of formulae. AVERAGE is OK with whole columns MEDIAN-IF is NOT.

[AliGW]

They ARE indeed dates!!!

[OrthopodAcademic]

You are both absolutely amazing

Many thanks to you both

Ben

[AliGW]

Try:

=MEDIAN(FILTER($I$2:$I$20,$D$2:$D$20=L2))

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	L	M	N
1	Month	Mean LOS	Median LOS
2	Aug-23	1.25	1
3	Sep-23	1	1
4	Oct-23	2.71428571	2
5	Nov-23	1.5	1.5

Sheet: Sheet1