Filter data and adapt mean value in Excel

I would like to filter an excel data list by one feature (part number) and then, when I deselect one part have it not be included into the calculation of e.g. the mean value. However the mean will stay the same, because it says =MITTELWERT(B5:B14) in the function line.

Does anybody have an easy solution for this?

Solution 1:

you can use subtotal to do this :

=SUBTOTAL(101, B5:B14)

You can use sub total for many other functions also, by replacing the 101 with any of the below:

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

Im not 100% sure but the German equivalent may be =TEILERGEBNIS ??

Solution 2:

AGGREGATE() using function #1 (Average) and option 5 (do not include hidden rows), or options 1, 3, or 7 (they ignore more things than just hidden rows) will also do the job:

=AGGREGATE(1,,A1:A9)    (for example)

because the function's code considers rows that are filtered out to be hidden in the same way that formally "hidden" rows are hidden.

An advantage though, to using SUBTOTAL(), is that there are other functions that can be told to ignore any SUBTOTAL() cells found in the range they are told to operate upon. AGGREGATE() will do that for itself, and for SUBTOTAL() but I am not aware, offhand, of any other functions that can be told to ignore AGGREGATE() functions in their operating ranges. If that would be any kind of concern, be sure to use SUBTOTAL().

If of interest, the German version name for the AGGREGATE() function is AGGREGAT. And for SUBTOTAL, it is TEILERGEBNIS, just as PeterH shows. A useful website for this is:

https://www.excelfunctions.eu/

(For now anyway. Web pages do come and go.)