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

 

[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" <[email protected]>
wrote in message
news:cac2ab21-9e3f-49f3-b2dc-8ff63be0f9ab@b34g2000yqi.googlegroups.com...

 

[joeu2004]

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

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

 

[joeu2004]

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.