Calculate Median using conditional based on dates

I know this has got to be simple for someone more versed in Excel. Here's what I want to do:

I have two columns. I is date of sale, J is sale price. I want to be able to calculate the median sale price for all sales within 1 month, say January. With my limited knowledge and looking at the forums this is what I've come up with but, it doesn't work.

=MEDIAN(if(I19:I35<=1/31/2007,J19:J35))

I know why it doesn't work. I don't know what I'm doing. But, I hope I've demonstrated I have at least tried. This is as far as I've been able to design the formula without further input. Hopefully, someone can help. I have searched the forums for about 2 hours with no luck.

Hello Shai-Hulud,

To check if the date is less than or equal to a given date and then display the median of the range or leave the cell blank, use the formula below.

Please Login or Register  to view this content.

"We have worm sign the likes of which God has never seen!"

Sincerely,
Leith Ross

Thanks for your reply and you're input. I'm a little bit closer. The formula you provided results in no calculation, just a blank cell. So, I toyed with it a bit and produced a result. It now calculates the median but, it doesn't do exactly like I need it to. Heres the formula: =MEDIAN(IF(I19:I35<=DATEVALUE("1/31/2007"),(J19:J35)))

I may not have fully described what I needed so I will attempt to do so. Values in I19 through I35 are dates that correspond to a sale price in J19 through J35. I'm trying to calculate the median for all sales <=DATEVALUE("1/31/2007"). Whereas now, it's calculating the median for all sale prices in the range J19 through J35 regardless of their corresponding dates.

Bumping to see if I can learn a little more.

You're not far away with your first attempt. Try

=MEDIAN(IF(I19:I35<="1/31/2007"+0,J19:J35))

This is an "array formula" and, as such, needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar