Excel Guides

Excluding Values from Averaging in Excel

When you average a range of cells in Excel, the program automatically includes all values in the range. However, there may be times when you want to exclude certain values from the average. For example, you may have a range that includes both positive and negative numbers, but you only want to average the positive numbers. Or, you may have a range that includes error values, but you want to average only the valid data points.

There are a few different ways that you can exclude values from averaging in Excel. One way is to use the FILTER function. This function allows you to specify which values you want to include in the average. For example, if you have a range of cells that contains both positive and negative numbers, you can use the FILTER function to only include the positive numbers in the average.

Another way to exclude values from averaging is to use the SUBTOTAL function. This function has an option to ignore hidden cells, so it can be used to exclude cells that have been filtered out or otherwise hidden from view. For example, if you have a range of cells that contains both valid data points and error values, you can use the SUBTOTAL function to only include the valid data points in the average.

Finally, another way to exclude values from averaging is by using conditional formatting. This method can be used if you want to exclude cells based on their contents. For example, if you have a range of cells that contains both text and numeric values, you can use conditional formatting to only include the numeric values in the average.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.