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

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
--
Bernard Liengme
Microsoft Excel MVP
people.stfx.ca/bliengme
email address: remove uppercase characters


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

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?

--
Rick (MVP - Excel)


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

Rick;
There is missing data which will be treat as zero.

Yes I wish to find the median of the 5th col selected by the date in 2nd
col. the data has over 1000 rows,

How do I select only the rows in the 1st quarter of 2008 and determine the
Median of the values in the 5th col.

=MEDIAN(IF(B19:B1250," ="&Today()-120, E19:E1250,"")
I've played with variations of this with no results.

Thanks



"Rick Rothstein" wrote:

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?

--
Rick (MVP - Excel)


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

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

On Jan 6, 12:59*am, barry houdini wrote:
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)))

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




"barry houdini" wrote:

On Jan 6, 12:59 am, barry houdini wrote:
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)))

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


"barry houdini" wrote:

On Jan 6, 12:59 am, barry houdini wrote:
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)))