Median IF using dates and days in between.

[Lenana]

Good afternoon forum,

As I am in my first day in this forum, first post as well, I will apologize if this has already been answered but I couldn't find the answer. My problem is using the median function under conditional criteria mainly dates. I've attached an example of the dates and days bewtween function. What I need to do is have the formula check the column "Date2" and if it falls in anytime in November 2012 then it will show me the median from the "Days Between" column. I know for this small of data I can use the regular =median(c7:c11) and it'll give me the answer of 21 but I am dealing with multiple years, months and trying to find a way to make this work on hundreds of records automatically.

Thank you in advance.

[oeldere]

You can use the formula month() and year().

Then you can use pivot table to count your data.

There is no (standard) function of median (in pivot table), but maybe it can be count with an helpcolumn.

I am not familiar with function median.

[daddylonglegs]

Try an "array formula" like this

=MEDIAN(IF(TEXT(B2:B11,"mmm-yy")="Nov-12",C2:C11))

formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar. If that's done correctly then you will get a result of 21 for your example

[Lenana]

Thank you Daddylonglegs. That's exactly what I needed for that example. It works perfectly. I'm hoping you can answer one more question still in regards to this example. If I wanted to find the median for a range of dates, (July 1,2012 - Sep 30,2012) would it be similar to the above solution?

For example - =MEDIAN(IF(B2:B11>=01/07/2012&<=30/09/2012,C2:C11))

I know that is not a correct formula but that's the best I can come up with.

[Lenana]

Re: Median IF using dates and days in between.
Thank you Daddylonglegs. That's exactly what I needed for that example. It works perfectly. I'm hoping you can answer one more question still in regards to this example. If I wanted to find the median for a range of dates, (July 1,2012 - Sep 30,2012) would it be similar to the above solution?

For example - =MEDIAN(IF(B2:B11>=01/07/2012&<=30/09/2012,C2:C11))

I know that is not a correct formula but that's the best I can come up with.

Try an "array formula" like this

=MEDIAN(IF(TEXT(B2:B11,"mmm-yy")="Nov-12",C2:C11))

formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar. If that's done correctly then you will get a result of 21 for your example

[daddylonglegs]

The simplest way is to put the dates in cells, e.g. with start date in D2 and end date in E2 try

=MEDIAN(IF((B$2:B$11>=D2)*(B$2:B$11<=E2),C$2:C$11))

or if you need dates in the formula use DATE function like this

=MEDIAN(IF((B$2:B$11>=DATE(2012,7,1))*(B$2:B$11<=DATE(2012,9,20)),C$2:C$11))