When working with filtered data, it is important for any formulas or functions to return accurate results. Typically, this isn't a concern because Google Sheets will automatically adjust as data is entered or removed. However, when data is filtered, the function results continue to take into account all of the data, not just the visible data.
In the following example, the AVERAGE function has been used to calculate the average number of books read.
NOTE: If the function is placed in a row adjacent to the data, the function will be included when the data is filtered or sorted. A simple way to avoid this is to place the function in a non-adjacent row.
When this data is filtered to show only the 11th-grade students, the average remains the same.
Even though they aren't visible, the AVERAGE function is still being applied to cells B2:B14. This is a simple example and it may be immediately obvious that the function isn't calculating just the visible rows. However, with a larger dataset, it may be more difficult to notice discrepancies.
To remedy this problem and calculate the average for just the visible rows, it is necessary to use the SUBTOTAL function.