Finding the Median

[Bad_Shot]

I need to determine the Median of one column, while selecting those rows in a
specific date range.

I need the Median of Col 5, using a date range of Col 2.


Sample Data
Sold 1/10/2008 188 6900000 188 6300000
Sold 1/14/2008 44 575000 135 556200
Sold 1/28/2008 181 349900 335000
Sold 1/31/2008 98 775000 292 757000
Sold 2/1/2008 7 319900 7 315000
Sold 2/8/2008 136 849000 136 750000

 

[Bernard Liengme]

If I have two columns (A1:B8) like this:
a 1
a 2
b 3
c 4
a 5
b 6
c 7
a 8
and I want the median of numbers in column B where column A values are "a",
then I use
=MEDIAN(IF(A1:A8="a",B1:B8,""))
Since this is an array formula I must commit it with CTRl+SHIFT+ENTER not
just ENTER
best wishes

 

[Rick Rothstein]

I presume you mean by the fifth column the one that starts off with 188,
135. If so, you appear to be missing the value for this column in the third
row. Did you accidentally leave out the value? Is it really supposed to be
an empty cell? Should it have been a 0? If it was deliberately left out,
should it be treated as a 0 or should it be ignored completely?

 

[barry houdini]

You aren't far off with that attempt, see Bernard's post for the
correct syntax...or try like this

=MEDIAN(IF(B19:B1250>=Today()-120,E19:E1250))

This is an array formula that needs to be confirmed with CTRL+SHIFT
+ENTER

 

[barry houdini]

You aren't far off with that attempt, see Bernard's post for the
correct syntax...or try like this

=MEDIAN(IF(B19:B1250>=Today()-120,E19:E1250))

This is an array formula that needs to be confirmed with CTRL+SHIFT
+ENTER

or if you want dates within "1st Quarter of 2008", assuming that means
1st Jan to 31st Mar 2008 try

=MEDIAN(IF(B19:B1250>=DATE(2008,1,1),IF(B19:B1250<=DATE
(2008,3,31),E19:E1250)))

 

[Bad_Shot]

Barry;
Thanks I got the one using date to work for the quarters, then realized I
needed it in 90 day increments. ie to determine the median from today
1/6/2009 back to 10/6/2008,etc.

I tried to play with this
{=MEDIAN(IF(B19:B125,"<="&TODAY()),IF(B19:B125,">="&TODAY()-90),G19:G115)}
But no matter how I do the <,>,= or change the TODAY()-nn it always gives me
the same result.

I think the syntax is worn some where.

Thanks Again

 

[Bad_Shot]

Barry;
I got the DATE(2008,1,1) format to work but require manual intervention each
time I need to change the date.

Trying use DATE(a1,a2,a3)....... DATE (b1,b2,b3) but I ned up with 9 cells
to manually input) Got to be a better way.
Thanks