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 have a pivot table with each row having an error code (say, widget defect code, with 0 being no error). I want to be able to summarize the data with total error rates, but I'd also like to summarize by excluding specific error types. So the basic forumula is obviously (rows with error code > 0) / (total rows). However, when you filter a pivot table, you remove some rows from the numerator, but they also get removed from the denominator.

Of course, if the number of rows with errors is much smaller than the total number of rows, this isn't such a big deal, but there are some other stats in the table that I'd like to preserve (ie, I don't want the rows removed for the purpose of those calculations)

Is there a way to filter so that the excluded rows are excluded only for the error calculation, but retained for the other summary values (widget count, widget weight)?

Hope that makes sense!

share|improve this question

1 Answer 1

The SUBTOTAL function is purpose built for this. It can do Average, Count and Sum (and many other things too) and can optionaly include or exclude hidden rows

share|improve this answer
    
I rather hope not to need the three digit Function_num but nice to know (now) they are available. Another gem! –  pnuts Dec 5 '12 at 9:11

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.