Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .