excel median(if) with dates as criteria

August 19, 2024 9:06 AM

In Excel, I have a spreadsheet where A:A is a column of dates and B:B is a column of numbers. I would like to calculate the median number for a given date. (Ultimately, for a given *range* of dates, but one step at a time.) It seems like =median(if(logical_test,range)) should work (e.g. 1, 2) but so far I am getting errors. Any help? Or is this just not possible in Excel?

I have been able to get a formula like this to work with C:C, containing text (=MEDIAN(IF(C:C="Y", B:B))) but something about working with the date in the logical test isn't working for me.

My attempts look something like =MEDIAN(IF(A:A=9/1/2023, B:B)). I've tried different combinations of quotes and not around the date and = but can't get anything to work. This returns a #NUM! error.

Notes: I understand that this is an array formula and needs Ctrl+Shift+Enter. I have confirmed that the date column is formatted as dates and the number column is formatted as numbers.

posted by oakroom to Computers & Internet (10 answers total)

Best answer: You likely need to add a DATEVALUE function (or store the date in a different cell), i.e.:

=MEDIAN(IF(A:A=DATEVALUE("9/1/2023"), B:B))

This is because although Excel presents dates in a human-readable format as "9/1/2023" or "1/9/2023" or "2023-09-01" (depending on the language and regional settings you have on your computer), it actually stores them as a number, which is calculated as the number of days since January 1, 1900.

So for example Sept 1 2023 is actually stored internally as something like "45170" (which I would expect incidentally to work if you put that in your formula). Excel can't match "45170" to "9/1/2023" when "9/1/2023" is a text string, but DATEVALUE gets around this.
posted by andrewesque at 9:15 AM on August 19, 2024 [4 favorites]

Response by poster: Bingo! Thank you!

The follow-on question is how to format the formula to compare dates. Suppose I want to say "before 12/1/23".

=MEDIAN(IF(A:A < DATEVALUE("10/1/2023"), B:B)

returns a value of 0, which isn't correct. Is there some other formatting around the <?
posted by oakroom at 9:37 AM on August 19, 2024

Assuming the dates are formatted as dates, use the filter() function:

MEDIAN(FILTER(B:B,(A:A>=D1*(A:A<>

D1 is start date and D2 is end date.
posted by mullacc at 9:46 AM on August 19, 2024

=MEDIAN(FILTER(B:B,(A:A=D1*(A:A=D2))))

sorry forgot how to fix the stripped parentheses.
posted by mullacc at 9:52 AM on August 19, 2024

Bah, I can't remember how to paste code correctly. Here it is: https://pastecode.io/s/ih93pbh6
posted by mullacc at 9:58 AM on August 19, 2024

Response by poster: mullacc, I was having trouble with that, I think FILTER must be a newer function and not available in Excel 2016, which I am using?
posted by oakroom at 10:02 AM on August 19, 2024

Response by poster: Marking andrewesque as best answer, since they solved the immediate question I was asking. I appreciate the suggestion, mullacc, but it doesn't seem like FILTER is available to me.

Continuing to play with MEDIAN(IF...), I am strangely finding that it seems to work for greater than/after a given date, but less than/before a given date returns a zero value. Anyone have any clues what's going on there?
posted by oakroom at 10:51 AM on August 19, 2024

I think this does it with Excel 2016: https://pastecode.io/s/wmxgf130

I had to make it ignore the zeros in the initial IF().
posted by mullacc at 11:50 AM on August 19, 2024

Response by poster: Hm. Does that want to be an array formula (Ctrl+Shift+Enter)? I get a #VALUE! error when I try that. (When I try it as a regular formula [just Enter], it produces an answer -- but it's not the correct median of those cells!)
posted by oakroom at 12:41 PM on August 19, 2024

Hmm. Works with ctrl+shft+enter or without for me. Might be a version issue. Sorry!
posted by mullacc at 1:29 PM on August 19, 2024