Excel Pivot table values

Question

I am trying to create a Pivot table in 2007 Excel that will show median instead of average (mean). Basically, I have a large set of data for how long many months it took to perform a certain task, the year it was completed, and the category of that task (categories A-D). I need to be able to calculate the median time for each category (A-D) for each year and also the combined median for only categories A and B, broken down by year. The Pivot Table worked for average, but I cannot figure out how to use it to calculate the medians.

As an alternative, I have also tried using a conditional formula, but cannot figure out the correct syntax for "OR". The formula I have tried to create is below:

=MEDIAN(IF(OR(all!$A$1:$A$65000 = "A", all!$A$1:$A$65000 = "B"),IF(all!$E$1:$E$65000 =   
"FY05", IF(all!$C$1:$C$65000 <> "",all!$C$1:$C$65000, ""), "")))

Answer 1

First, there is no built-in way to do medians in a pivot table. This lack of functionality created so much work for me on past projects, you wouldn't believe it.

Second, you're right; OR() does something weird in array formulas. Try this to get around the problem:

=MEDIAN(IF(all!E1:E65000="FY05",IF(all!A1:A65000="A",all!C1:C65000,IF(all!A1:A65000="B",all!C1:C65000,"")),""))

Be sure to enter this as an array formula. Do so by pressing Ctrl+Shift+Enter to enter the formula.