TRIMMEAN in a Filtered Table

[Trethia]

Hello,
I have been struggling with including a TRIMMEAN formula into my data because I have a table that is filtered so it just calculates the entire array, when I only want it to show calculations for visible cells. I am calculating Delay and I have an entire column for Delay with numbers underneath that I want the TRIMMEAN to apply for at all times, even when the filter is on. I would like it to be similar to the AGGREGATE function in the sense that it will recalculate the data after the filter changes the table.

Does anyone have any suggestions?

 

[Domenic]

Let's say that A2:A100 contains the data, the following formula will return the result based on the filtered data...

=TRIMMEAN(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1))>0,A2:A100),0.2)

To also exclude rows that are manually hidden, try the following formula instead...

=TRIMMEAN(IF(SUBTOTAL(103,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1))>0,A2:A100),0.2)

Adjust the range and change the percent argument for SUBTOTAL accordingly.

Note that earlier versions of Excel need to confirm the formula with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Trethia]

Let's say that A2:A100 contains the data, the following formula will return the result based on the filtered data...

=TRIMMEAN(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1))>0,A2:A100),0.2)

To also exclude rows that are manually hidden, try the following formula instead...

=TRIMMEAN(IF(SUBTOTAL(103,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1))>0,A2:A100),0.2)

Adjust the range and change the percent argument for SUBTOTAL accordingly.

Note that earlier versions of Excel need to confirm the formula with CONTROL+SHIFT+ENTER.

Hope this helps!

It worked well! Thank you so much for replying quickly