Calculating an Average of Visible Cells

Gary routinely filters data in a worksheet based on various criteria. He would like to calculate an average of the visible values in column C. In other words, when he filters the data, he wants the average of what is visible in column C after the filter is applied. Gary tried the AVERAGE function, but it seems to apply to all cells in column C regardless of their visibility.

There are several ways you can accomplish the task. First, Gary could convert his data to formal table and then add a total row. You can then select the total shown in column C, click the down-arrow next to the total, and choose Average. This will average just the visible values in the table, which is exactly what Gary wants.

The way that adding an average in a formal table is accomplished leads to the next option: using the SUBTOTAL function. If you perform the above steps for a formal table, you can see that Excel calculates the average using this function:

=SUBTOTAL(101,[Amount])

The second parameter is the name of the table column being averaged. There are two things to note here, however. First, SUBTOTAL doesn't work with just formal tables. You can specify a range that should be averaged by using the second parameter:

=SUBTOTAL(101,C2:C1000)

This means that the SUBTOTAL approach works great if you need your average somewhere else besides in a total row.

The second thing to note is the first parameter, in this case 101. The general idea of this parameter is to specify what you want returned by SUBTOTAL. If you want an average returned (as does Gary), then you can use a first parameter value of either 1 or 101. The difference between the two is in how SUBTOTAL handles manually hidden rows. If the value is 1, then manually hidden rows are included in the average. If the value is 101, then manually hidden rows are excluded from the average.

Understand that filtered rows are always ignored by SUBTOTAL; the first parameter only deals with manually hidden rows. This can cause some seemingly wonky results if your data includes both filtered rows and manually hidden rows. In that case, SUBTOTAL ignores all hidden rows, regardless of whether you use 1 or 101 as your first parameter.

If you are using Excel 2010 or a later version, you can use the AGGREGATE function instead of SUBTOTAL. Why would you want to do this? Primarily because AGGREGATE offers better control over what should be excluded from your average and it natively works with other array-based functions. There are three parameters that should be specified with AGGREGATE, in this manner:

=AGGREGATE(1,3,C2:C1000)

The first parameter indicates you want an average returned by the function. The second parameter indicates what you want to exclude from the calculation, as follows:

To the AGGREGATE function, it doesn't matter how rows are hidden; filtered rows and manually hidden rows are treated the same. Whereas SUBTOTAL always excludes filtered rows, AGGREGATE only excludes them (along with manually hidden rows) if you set the second parameter to 1, 3, 5, or 7.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13557) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.