Q&A

How do you find the average subtotal?

How do you find the average subtotal?

After you filter the rows in a list, you can use the SUBTOTAL function to average the visible rows.

1. Apply an AutoFilter to the table.
2. Filter at least one of the columns in the table.
3. Select the cell immediately below the column you want to sum.
4. Click the AutoSum button on the Ribbon’s Home tab or Formulas tab.

How do I keep the subtotal function from changing when I re filter cells?

In short the answer is No, it is not possible. Subtotal will change every time you change your filter. This is the way Subtotal is designed and you can’t control this behavior.

How do you filter subtotals?

Therefore, the solution is to use the Subtotal function, which only calculates the visible cells in a range.

1. Display workbook in Excel containing data to be filtered.
2. Click anywhere in the data set.
3. Apply filter on data.
4. Click below the data to sum.
5. Enter the Subtotal formula to sum the filtered data.

How do you use the average function?

Average, which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.

How do you use Subtotal function?

Insert subtotals

1. To sort the column that contains the data you want to group by, select that column, and then on the Data tab, in the Sort & Filter group, click Sort A to Z or Sort Z to A.
2. On the Data tab, in the Outline group, click Subtotal.
3. In the At each change in box, click the column to subtotal.

How do you use the average function in Excel?

AutoSum lets you find the average in a column or row of numbers where there are no blank cells.

1. Click a cell below the column or to the right of the row of the numbers for which you want to find the average.
2. On the HOME tab, click the arrow next to AutoSum > Average, and then press Enter.

How does subtotal work in a filtered list?

In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num. SUBTOTAL ignores other SUBTOTAL formulas that exist in references to prevent double-counting. SUBTOTAL is designed to work with vertical data values arranged vertically. In horizontal ranges, values in hidden columns are always included.

When to use subtotal or average function in Excel?

If the AVERAGE function is applied in the filtered data or list, it will not get the correct average. In this condition, the SUBTOTAL function can help you average filtered data/list in Excel. Supposing you have filtered your table as following screen shots shown, you can get its average as follows:

How to subtotal only the visible cells after filtering in Excel?

In the formula, number 9 means that you specify the sum function to the subtotal; C2:C13 is the range you will subtotal. You can change the number 9 to any number mentioned in the below table based on your needs. From now on, when you filter the column data, the SUBTOTAL function only sum the visible cells as the below screenshot shown.

When to use autosum in the subtotal function?

The Autosum function will base it’s calculation on the visible cells. If the first visible row is not the first row in the table (i.e. the first row is filtered out) it will not be included when the SUBTOTAL function is created using the Autosum feature. . .