Excel Trick: Computing an Average of Filtered Data

As everyone in the office seems to be out early on Christmas break, I am using all this time without meetings to do some analysis work. Yesterday I did a lot in Excel and came across yet another trick that I want to remember and so I thought I would put it here. Hopefully it helps somebody else out there.

The spreadsheet I am playing with has over 32,000 rows and 44 columns. Some of those columns don't have any data and so I have been filtering the rows to see just the data I am interested in analyzing. Yesterday I created a filter to see all the rows that had values in one column and reduced the number of rows down to 16,000. That cut my data in half but is more data than I can casually scroll through.

I removed my first filter and filtered on another column and got the number of rows down to 4,000. That is approaching an acceptable amount of data to review. I wanted to reduce it even further and so combined the 2 filters and reduced the amount of data to 1,500 rows. These rows turned out to be key for my analysis. Now I needed to average the values in a 3rd column.

I tend to use Excel a lot but don't consider myself a power user. Well at least not yet. I used the AVG function and got a number. Then I removed my filters and discovered that average did not change. That means that the AVG function works on cells even if they are hidden by a filter. Instead I needed to use the SUBTOTAL function. It only works on viewable data and can be used for a number of different calculations.

The first parameter in Excel's SUBTOTAL function is a number that signifies the mathematical calculation you want to perform. The numbers correspond to the following:

• 1 = Average
• 2 = Count of all values even if they are blank
  
• 3 = Count of all values that are not blank
  
• 4 = Maximum
• 5 = Minimum
• 6 = Product
• 7 = Standard Deviation if the data represents a sample
  
• 8 = Standard Deviation if the data represents the entire population
  
• 9 = Sum

There are more, but you get the idea. Personally if I was the creator of Excel I would have associated sum with the number 1 as I think a subtotal is a sum and not an average of values. I guess Microsoft never won any awards for being intuitive.

Once I used the SUBTOTAL function the math worked out and my hypothesis about the data turned out to be true. Furthermore I can go above and beyond the simple statement that customers in group A are more valuable than group B. I can say they spend 20% more and that is a significant amount.