How to get average of groups of n from filtered table in excel

Excel is a powerful tool that can help you organize and analyze data. One common task is finding the average of groups of numbers from a filtered table. In this article, we will guide you through the steps to achieve this in Excel.

Step 1: Filter your table

The first step is to filter your table based on the criteria you want to use for grouping. To do this, follow these steps:

1. Select the range of cells that contains your data.
2. Go to the "Data" tab in the Excel ribbon.
3. Click on the "Filter" button. This will add filter arrows to each column header.
4. Click on the filter arrow for the column you want to use for grouping.
5. Select the criteria you want to use for grouping. For example, if you have a column with product categories, you can select a specific category to filter the table by.

Step 2: Insert a helper column

In order to calculate the average of each group, we need to insert a helper column that will assign a unique identifier to each group. Follow these steps to insert the helper column:

1. Insert a new column next to your data. This column will be used for the helper values.
2. In the first cell of the helper column, enter the following formula: =IF(ISFILTERED(A2), MAX($B$1:B1)+1, "")
3. Drag the formula down to fill the helper column for all rows in your table.

Step 3: Calculate the average for each group

Now that we have our helper column, we can calculate the average for each group. Follow these steps:

1. Insert another column where you want to display the average values.
2. In the first cell of the average column, enter the following formula: =IF(ISNUMBER(B2), AVERAGEIFS(C:C, B:B, B2), "")
3. Drag the formula down to fill the average column for all rows in your table.

Now you should have the average values for each group displayed in the average column. If you change the filter criteria, the averages will automatically update to reflect the new groups.

Example

Let's say you have a table with sales data for different products and you want to calculate the average sales for each product category. Here's how you can do it:

1. Filter the table by the desired product category.
2. Insert a helper column next to the data.
3. Enter the helper values using the formula =IF(ISFILTERED(A2), MAX($B$1:B1)+1, "")
4. Insert another column for the average values.
5. Enter the average formula =IF(ISNUMBER(B2), AVERAGEIFS(C:C, B:B, B2), "")

Now you can easily see the average sales for each product category, even when the table is filtered.

Conclusion

Calculating the average of groups of numbers from a filtered table in Excel can be a useful skill for data analysis. By following the steps outlined in this article, you can easily accomplish this task and gain valuable insights from your data.

References