Simple, simple Excel formula for working with filtered results only

Question

I have a dataset with over 1000 records. I can filter them using an automatic filter. At the bottom, I have some simple formulas like AVERAGE () and QUARTILE (). I would like to ensure that when filtering data, the results of AVERAGE should change, so they should only consider visible data.

I searched google here too, but can't find anything like it except using "subtotal", but this is not for the formulas I want to use.

Is it possible? Thanks,

Answer 1

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 can use:

 =SUBTOTAL(101, yourRange) 

Answer 2

I think you should use a pivot table.