Median If in Excel not working

[Ronen Hefetz]

I am trying to do a conditional median in Excel. I have 2 columns that
looks like this:

I J
2/7/2011 5
4/6/2011 6.4
5/10/2011 4.4
7/6/2011 4.7
8/23/2011 4.8
9/20/2011 11.8
9/20/2011 4.8
10/4/2011 6.3

I want the median of the cells in B1 where the date in A1 is prior to
today, held in cemm M1.

I tried =MEDIAN(IF(I2:I50<M1,J2:J50)), but that is returning the
median of all values, whether or not the corresponding value in column
J is prior to the date in M1.

I tried searching the forum, but didn't come up with anything.

Any help would be greatly appreciated.

Thanks in advanced.

[Claus Busch]

Hi,

Am Fri, 29 Jul 2011 08:49:21 -0700 (PDT) schrieb Ronen Hefetz:

> I am trying to do a conditional median in Excel. I have 2 columns that
> looks like this:
>
> I J
> 2/7/2011 5
> 4/6/2011 6.4
> 5/10/2011 4.4
> 7/6/2011 4.7
> 8/23/2011 4.8
> 9/20/2011 11.8
> 9/20/2011 4.8
> 10/4/2011 6.3
>
> I want the median of the cells in B1 where the date in A1 is prior to
> today, held in cemm M1.

try:
=SUMIF(I2:I50,"<"&$M$1,J2:J50)/COUNTIF(I2:I50,"<"&$M$1)

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Jim Cone]

Seems to work for me if entered as an array formula by pressing: Ctrl + Shift + Enter
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)

"Ronen Hefetz" <rhe...@gmail.com>
wrote in message
news:cac2ab21-9e3f-49f3...@b34g2000yqi.googlegroups.com...

[joeu2004]

"Claus Busch" <claus...@t-online.de> wrote:
> try:
> =SUMIF(I2:I50,"<"&$M$1,J2:J50)/COUNTIF(I2:I50,"<"&$M$1)

That is the mean (average), not the median.

[joeu2004]

"Ronen Hefetz" <rhe...@gmail.com> wrote:
> I tried =MEDIAN(IF(I2:I50<M1,J2:J50)), but that is
> returning the median of all values, whether or not
> the corresponding value in column J is prior to the
> date in M1.

That would be the case if I2<M1 by coincidence and you simply pressed Enter
instead of ctrl+shift+Enter.

The above formula should be entered as array formula.

Enter an array formula by pressing ctrl+shift+Enter instead of Enter. Excel
will display an array formula surrounded by curly braces in the Formula Bar,
i.e. {=formula}. You cannot type the curly braces yourself. If you make a
mistake, select the cell, press F2 and edit, then press ctrl+shift+Enter.

While pressing and holding Ctrl and Shift, press Enter. Then release all
three keys.