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, ""), "")))