Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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, ""), "")))
share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.