excelfilterformula

Excel simple formula to work only on filtered results

I have a set of data with over 1000 entries. I can filter these using the auto filter. At the bottom, I then have some simple formulas, for example AVERAGE() and QUARTILE(). What I would like to achieve is that when I filter the data, the results from AVERAGE should change, so they should only take into account the visible data.

I've been searching google and here, but can't seem to find anything like this, other than using "subtotal", but that's not for the formulas I want to use.

Is this possible? Thanks,

Solution

  • Use SUBTOTAL

    Function_num    Function_num        Function 
(incl hidden)   (ignores hidden) 
1                   101             AVERAGE 
2                   102             COUNT 
3                   103             COUNTA 
4                   104             MAX 
5                   105             MIN 
6                   106             PRODUCT 
7                   107             STDEV 
8                   108             STDEVP 
9                   109             SUM 
10                  110             VAR 
11                  111             VARP

    So you could use :

    =SUBTOTAL(101, yourRange)