trimmean filtered rows

[lcornaro]

Hi, I'm trying to get the trimmean of filtered rows. For an unfiltered worksheet this is my equation: =TRIMMEAN(Q2:Q3478;0.05)

 

[Andrew Poulsom]

Try:

=TRIMMEAN(IF(SUBTOTAL(109,OFFSET(Q2:Q3478,ROW(Q2:Q3478)-ROW(Q2),,1,1)),Q2:Q3478),0.05)

confirmed with Ctrl+Shift+Enter.

 

[XOR LX]

Hi,

Try this array formula (important that you know how to enter this type of formula in Excel):</SPAN>

=TRIMMEAN(IF(SUBTOTAL(9,OFFSET(Q2,ROW(Q2:Q3478)-MIN(ROW(Q2:Q3478)),,,)),Q2:Q3478),0.05)</SPAN>

Regards

 

[lcornaro]

thank you guys for the quick reply! you dont know how many countless of hours of trial and error you spared me!