Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Filtered Data in Excel

Filtered data in Excel, average values from the filtered column (display specific data from the filtered data table)

In this 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.


=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.

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 copies 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 sheet 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 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 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 cells 'A24' and 'A27' 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 ćelijie 'A24', ie. 2002 years in the range "A2:A21").

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

In the 27 row, 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 cells 'B30', 'B31' and 'B34'. They'll explain later at the end of this tutorial.

The calculation of the average 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 function SUBTOTAL.

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.

The 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 be 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 value 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) 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 nested formula OFFSET. 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')

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

The calculation of the average value if you manually hide rows or cells

In the following 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/cells range 'B2: B21').

Calculate the average value of the data range when the rows are hidden manually
BTW: 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 the comma