Array Median?

[Ody]

Hello all,

I have a tricky scenario I'm trying to figure out and could use some more brains.

I have a team of 8 people who take calls. I need to get the median (and average) value of the total number of calls for these people for a specified date range, say 1/1/2013 to 1/24/2013.

When i use the following f(x):

{=MEDIAN(IF((Call_Data!$A$2:$A$13000>=$E$8)*(Call_Data!$A$2:$A$13000<=$E$9),(Call_Data!$D$2:$D$13000)))}

I get the median value by day, not the median value for the date range for all people.

In this example column A has my dates, column D has the total calls per day, and (though not listed) column B has the employee names. Each row in this spread sheet is at the daily level.

Any thoughts?

Thanks.

 

[cbuchman]

A pivot table can easily provide the average, but I don't see median in the list for calculations, but it might be worth some investigation. With the arrary formula, I believe you will need to use nested if statements - e.g., if (date>=low_date, if (date<=high_date, values,0),0)