Median If in Excel not working
 

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.

Median If in Excel not working
 

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

Median If in Excel not working
 

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"
wrote in message
...
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.

Median If in Excel not working
 

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

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

Median If in Excel not working
 

"Ronen Hefetz" 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.