Calculating Median if Passes Logic Test

[JMHoustonTX]

Hello, I am stumped! I need help to calculate the median value in Column B if Date in Column A is within a certain month.
For Instance:

A B
1 1/1/2014 34
2 2/5/2014 45
3 1/27/2014 15
4 1/15/2014 7
5 2/27/2014 15


The median for January would be 15.

I thought I could use this:
{=median(if(sumproduct((A:A>=datevalue("1/1/2014"))*(A:A<=datevalue("1/31/2014))),b:b)}

This returns the median for all of column B. Anyone have any ideas?

Thank you!

 

[Domenic]

Try...

=MEDIAN(IF(A2:A100>=DATEVALUE("1/1/2014"),IF(A2:A100<=DATEVALUE("1/31/2014"),B2:B100)))

...confirmed with CONTROL+SHIFT+ENTER. Adjust the ranges, accordingly. Try to avoid using whole column references, since array formulas such as this one are resource intensive.

Hope this helps!

 

[JMHoustonTX]

Thank you for your help!
I'm not sure what my error is but that formula isn't working for me.
The answer should be 54, and if the entire column was calculated (like with my first formula), the answer would be 42. The new formula gives me 32.. Im not sure what it is calculating =/

Any other suggestions?
Any help is much appreciated.

 

[Domenic]

First, make sure that you confirmed the formula with CONTROL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula. Then make sure that the dates in Column A are true date values. You can use ISNUMBER to test for a true date value. So, for example, the following formula returns TRUE if the cell contains a true date value...

=ISNUMBER(A2)

Does this help?

 

[JMHoustonTX]

I still cant get it to work. There are empty cells in column B, do you think this could be the culprit?

Maybe I could attack this from a new angle with something like:

=MEDIAN(IF(MONTH(A2:A1500)=1,B2:B1500)) as an array.

Could that work? (It doesn't right now, haha)

 

[Domenic]

In this case, empty cells will be treated as zeros. Therefore, to ignore empty cells, try...

=MEDIAN(IF(A2:A100>=DATEVALUE("1/1/2014"),IF(A2:A100<=DATEVALUE("1/31/2014"),IF(ISNUMBER(B2:B100),B2:B100))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[JMHoustonTX]

Oh wow, you are good.

THANK YOU!!!!!!!!!!!!!!!!!!!!

 

[Domenic]

You're very welcome! Glad I could help!

Cheers!