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.

## Understanding the SUBTOTAL function

The SUBTOTAL function can be used to perform other standard functions including AVERAGE, COUNT, and SUM. The SUBTOTAL function differs from other Google Sheets functions because it requires a function code.

The syntax of the Subtotal is:

=SUBTOTAL(function_code, range)

Depending on what calculation you want the SUBTOTAL function to perform, you will use the corresponding function code.

Function | Code |

AVERAGE | 1 |

COUNT | 2 |

COUNTA | 3 |

MAX | 4 |

MIN | 5 |

PRODUCT | 6 |

STDEV | 7 |

STDEVP | 8 |

SUM | 9 |

VAR | 10 |

VARP | 11 |

To skip the hidden values, add 10 to the single-digit codes or 1 to the 2-digit codes. For example, the code for AVERAGE would become 101.

By using the SUBTOTAL function to find the average number of books read, hidden values are not calculated. When we filter the data to see only the 11-grade students, the result changes to show the average of the visible values.