TRIMMEAN In A Filtered Table

[arichar3]

Hi All,

Hopefully someone can help me out with this. Been trying for awhile with no luck.

I'm trying to build a TRIMMEAN function to use on a table that will be filtered. I continue to get the entire table's mean despite the filters. The cells being calculated are all functions themselves and a number are returning errors (#N/A) due to missing data. Not sure if this matters.

=TRIMMEAN(IFERROR(Table1[Days as Lead],""),25%)

Appreciate any help you can provide,

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=TRIMMEAN(IF(ISNUMBER(Table[Days as Lead]),Table[Days as Lead]),25%)

This should work as intended.

 

[Aladin Akyurek]

If you are applying autofilter to the table, the formula needs reworking...

 

[arichar3]

Thanks for the quick reply but it's still not changing the average when I filter the rows. I created Filter Buttons on the table to filter by other rows in the same table. Is that what you mean?

 

[Aladin Akyurek]

Thanks for the quick reply but it's still not changing the average when I filter the rows. I created Filter Buttons on the table to filter by other rows in the same table. Is that what you mean?

Control+shift+enter, not just enter:

=TRIMMEAN(IF(SUBTOTAL(2,OFFSET(Table1[@[Days as Lead]],
    ROW(Table1[Days as Lead])-ROW(Table1[@[Days as Lead]]),0,1)),
    IF(ISNUMBER(Table1[Days as Lead]),Table1[Days as Lead])),25%)

 

[arichar3]

Ah, there we go. Thank you so much for your help. Greatly appreciated.

 

[Aladin Akyurek]

Ah, there we go. Thank you so much for your help. Greatly appreciated.

You are welcome. Thanks for the update.