Blue Flower

Filtered Data in Excel

Average Values from the Filtered Column
(Display Specific Data from the Filtered Table)

About Filtering Data in Excel I wrote in the tutorial at the respective link. In this Excel example tutorial I will show how you can filter the data in the column according to certain criteria and to Display a Number of Visible Rows from the Filtered Table or a filtered range of data and display the average the values of the filtered range of cells.

Starting situation 1

In this situation in the picture below you see the data range 'A1:D9'. Also note that the data is sorted descending by the column 'D'.

The cell 'F13' you notice the criteria for further calculations, this is the requirement that we want to show remained the Data from the Filtered Excel spreadsheets.

ARRAY formula (CSE) in the cell 'F13': This formula returns the data from the last cell in column 'D'

=INDEX($C$2:$C$9;MAX(IF(SUBTOTAL(3;OFFSET($A2;ROW($C$2:$C$9)-ROW(C2);0));ROW($C$2:$C$9)-ROW(C2)+1)))

The range of cells 'A13:D16' you note Data showing the first three rows of the Filtered Range of cells above. So, irrespective which we criterion or Condition in column 'D' first range chosen, the lower table will display data for the selected criteria. (please see image below).

 Displaying results first three rows from the filtered range of cells in Excel

Result situation 1st

In the picture below you see that the data of the entire upper range of cells filtered by the column 'C'. Data from the last row in column 'C' we see in row 8 and the result of the formula in the cell 'F13'

ARRAY formula (CSE) in the cell 'A14' is as follows: (This formula is searching the Filtered Data range above and returns the first three rows of data for the condition in the cell 'F13'. This formula copy to the right, and then copy whole row down. When making copies the formula will automatically change the necessary address range of cells, and adapt to the needs of results, because it contains absolute and relative addresses.)

=INDEX(A$2:A$9;SMALL(IF($F$13=(IF(SUBTOTAL(103;OFFSET($C$2:$C$9;MATCH(ROW($C$2:$C$9);ROW($C$2:$C$9))-1;0;1));$C$2:$C$9));MATCH(ROW($C$2:$C$9);ROW($C$2:$C$9));"");ROW($A1)))

 Filtered data Excel spreadsheets

If you want to avoid the Excel worksheet cell 'F13' and the result of it.

If you wish, you can adjust cell addresses and the range of data and Nested Formula for the Condition (criterion) in the basic formula for returning results in a cell 'A14'.

Then the ARRAY formula (CSE) in the cell 'A14' to be next. (If you copy this formula in your Excel Worksheet then note that the formula is in one line (not broken as is the case on this site). Copy a formula to the right to 'D14' and then select the 'A14:D14' and copy down to the desired order).

=INDEX(A$2:A$9;SMALL(IF(INDEX($C$2:$C$9;MAX(IF(SUBTOTAL(3;OFFSET($A$2;ROW($C$2:$C$9)-ROW($C$2);0));ROW($C$2:$C$9)-ROW($C$2)+1)))=(IF(SUBTOTAL(103;OFFSET($C$2:$C$9;MATCH(ROW($C$2:$C$9);ROW($C$2:$C$9))-1;0;1));$C$2:$C$9));MATCH(ROW($C$2:$C$9);ROW($C$2:$C$9));"");ROW($A1)))

Of course you can, if you want to display multiple rows of results (not just 3 rows). Just to copy the formula to the desired number of rows. Also, this is just a simple example of data, you can use this formula to a wider range of data.

Average the Values in the Range of Data in Excel Spreadsheets

In the picture below you see a different situation Data Range. Column 'A' contains the year.

The column 'B' contains the values for a certain year. You notice that the repeated, then we have duplicates in column 'A'.

I have to set a few Excel examples of how we can calculate the Average Value of visible rows of a specified range of Data. I also set the formula by which we are counting certain criteria.

 Calculate the average value of the data from Excel spreadsheets

 

In the picture above you see the following results and the formula:

In the 'A24' and 'A27' cells is a Condition (criterion) for which we want to calculate a specific result. In the 24 row, there are two identical results for one condition or the two different formulas.

ARRAY formula (CSE) in the cell 'B24' is as follows. (This formula counts the number of times the criterion of cells 'A24', ie. 2002 years in the range "A2:A21").

=SUMPRODUCT(SUBTOTAL(3;OFFSET($A$2:$A$21;ROW($A$2:$A$21)-MIN(ROW($A$2:$A$21));;1));ISNUMBER(SEARCH(A24;$A$2:$A$21))+0)

ARRAY formula (CSE) in the cell 'C24' is as follows. (This formula counts the number of times the criterion of 'A24' cell, ie. 2002 years in the range "A2:A21").

=SUMPRODUCT(SUBTOTAL(3;OFFSET(B2;ROW(2:21)-2;))*($A$2:$A$21=A24))

In the row 27, there are two identical outputs and two different formulas. These two results will be the same when we filtered the data range. Also in the picture above you see the results in the 'B30', 'B31' and 'B34' cells. They'll explain later at the end of this tutorial.

The Calculation of the aAerage the Values when the Filtered One Column.

You notice in the picture below that I made a filtering table data by column 'A'. To filter I have set the year 2002. Excel is filtered display four rows of data. You see results in the cells 'B27' and 'C27'. The result is the same as in the picture above. So we are tied to the condition of the cells 'A27'.

What I want to explain the different result in the cells 'B30' and 'B31'. Look at the picture above. There is a result of the respective cell and the average value of 432.6 in the picture below this result is the average the values 1725. So, the same as in the cells 'B27' and 'C27'. Here I used the Excel SUBTOTAL function.

The formula in cell "B30" is as follows: (This function returns the average value of only the visible rows, manually hidden rows ignored). Here is the problem with the filtered rows and therefore it is necessary to create another formula as in the cell 'B27'.

=SUBTOTAL(101;B2:B21)

The formula in cell "B31" is as follows: (This function returns the Average Value for all rows/cells in a range of data, irrespective whether the rows manually hidden or filtered).

=SUBTOTAL(1;B2:B21)

So both listed SUBTOTAL formula with the argument '1' and '101' shows the same result with the filtered data. Always return the average value for the visible rows/cells.

 Calculate the Average value of the data filtered Excel table

In the picture above you see the formula in cell "B34". This formula counts the number of visible cells or rows, ie, returns the number of visible cells in the specified range. Therefore, this formula takes into account the data filtering.

SUBTOTAL formula in cell "B34" is as follows: (Number 2 in the formula below, indicates the first argument (Function_Num) counting)

=SUBTOTAL(2;A2:A21)

The Calculation of the Average the Values when the Filtered Two Columns in a Range of Data

You see in the picture below that I made an additional filtering column 'B', so we have only three values shown in the "table". Now you see the calculations of the Average value (AVERAGE) to show filtered data ie. the formulas in cells. That we are in this case used the AVERAGE function, then give us back the result of the formula 432.6 because that would taken into consideration all the values in the above mentioned range of data column 'B'.

=AVERAGE(B2:B21)

I must get back to the cell 'B27'. In the this cell there is a formula that returns the result of the Average Values of the Filtered Table, in column 'B' for the condition in column 'A'.

ARRAY formula (CSE) in the cell 'B27' is as follows. (This formula calculates the Average Value of the Filtered Data in column 'B' for the condition in column 'A'). Number 2 in the formula below indicates the first argument (Function_Num) of COUNT.

=IFERROR(AVERAGE(IF(SUBTOTAL(2;OFFSET(B$2;ROW(B$2:B$21)-ROW(B$2);0;1));IF(A$2:A$21=A27;B$2:B$21)));"")

If you look at the formula above, in her is OFFSET Nested Formula. In reality, if you look at the Evaluate Formula, thus nested function

 

OFFSET function in the formula returns (Press F9 and see the result below).
OFFSET(B$2;ROW(B$2:B$21)-ROW(B$2);0;1)

result =
{101;102;103;104;105;106;107;108;109;110;111;125;113;900;115;116;117;1000;2000;3000}

 

IF function return
IF(A$2:A$21=A27;B$2:B$21)

result = {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;900;FALSE;FALSE;FALSE;1000;2000;3000}

 

SUBTOTAL function returns the result (note, the last three units. They indicate the last three the values in the result IF function above, and accordingly takes into account the particular the values.

SUBTOTAL(2;OFFSET(B$2;ROW(B$2:B$21)-ROW(B$2);0;1))

result =
{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;1}

 

AVERAGE function/formula also returns the result as the IF formula but without the values 900. Therefore, ultimately Average calculates the average for the respective three values: (1000+2000+3000=6000)/3=2000
AVERAGE(IF(SUBTOTAL(2;OFFSET(B$2;ROW(B$2:B$21)-ROW(B$2);0;1));IF(A$2:A$21=A27;B$2:B$21)))

result =
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;1000;2000;3000}

 Average the values filtered data for two columns

So, this ARRAY (CSE) formula (cells 'B27') returns the Average Value of the Filtered Table (filtered column or filtered data)

=IFERROR(AVERAGE(IF(SUBTOTAL(2;OFFSET(B$2;ROW(B$2:B$21)-ROW(B$2);0;1));IF(A$2:A$21=A27;B$2:B$21)));"")

The formula in cell 'C27' is as follows: (This formula returns the Average of all Cells/Rows in Range for a specified condition in column 'A'). You note, in this case I used AVERAGEIF function.

=AVERAGEIF($A$2:$A$21;A27;$B$2:$B$21)

The Calculation of the Average Values if you Manually Hide Rows

In the the following Excel example, you notice, different results of the formulas above. In this case, I manually hide all rows from 'A2:A18'. You see in the picture below that the formula in cell "B27" does not display the correct result. But therefore, the formula in cell "B30" shows the correct result (This formula has nothing to do with the criteria, it calculates the Average Value of Visible Rows or Cells Range 'B2:B21').

Calculate the average value of the data range when the rows are hidden manually 

Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.