Using TRIMEAN on a filtered list

Hi,

I want to apply TRIMEAN to a filtered data set. TRIMEAN ignores a set percentage of outliers. I am currently using AVERAGE embedded in SUBTOTAL, but there is no option to embed TRIMEAN within SUBTOTAL. Any suggestions on how I could find a solution to this?

Bertie

Bertie,

You need to embed your filtering criteria within the TRIMMEAN function using an IF statement, along
the lines of:

=TRIMMEAN(IF(B1:B15="A",A1:A15,""),20%)

HTH,
Bernie
MS Excel MVP


"claytorm" <claytorm.1u7uma_1124798704.3997@excelforum-nospam.com> wrote in message
news:claytorm.1u7uma_1124798704.3997@excelforum-nospam.com...
>
> Hi,
>
> I want to apply TRIMEAN to a filtered data set. TRIMEAN ignores a set
> percentage of outliers. I am currently using AVERAGE embedded in
> SUBTOTAL, but there is no option to embed TRIMEAN within SUBTOTAL. Any
> suggestions on how I could find a solution to this?
>
> Bertie
>
>
> --
> claytorm
> ------------------------------------------------------------------------
> claytorm's Profile: http://www.excelforum.com/member.php...o&userid=11610
> View this thread: http://www.excelforum.com/showthread...hreadid=398108
>

Bertie,

Sorry, forgot to state that the formula as written must be entered as an array formula, using
Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:OkzcI$9pFHA.1024@TK2MSFTNGP09.phx.gbl...
> Bertie,
>
> You need to embed your filtering criteria within the TRIMMEAN function using an IF statement,
> along the lines of:
>
> =TRIMMEAN(IF(B1:B15="A",A1:A15,""),20%)
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "claytorm" <claytorm.1u7uma_1124798704.3997@excelforum-nospam.com> wrote in message
> news:claytorm.1u7uma_1124798704.3997@excelforum-nospam.com...
>>
>> Hi,
>>
>> I want to apply TRIMEAN to a filtered data set. TRIMEAN ignores a set
>> percentage of outliers. I am currently using AVERAGE embedded in
>> SUBTOTAL, but there is no option to embed TRIMEAN within SUBTOTAL. Any
>> suggestions on how I could find a solution to this?
>>
>> Bertie
>>
>>
>> --
>> claytorm
>> ------------------------------------------------------------------------
>> claytorm's Profile: http://www.excelforum.com/member.php...o&userid=11610
>> View this thread: http://www.excelforum.com/showthread...hreadid=398108
>>
>
>

=TRIMMEAN(IF(SUBTOTAL(3,OFFSET($C$3:$C$400,ROW($C$3:$C$400)-ROW($C$3),,1)),$C$3:$C$400),0.1)

where 0.1 is a percentage value.

The formula must be confirmed with control+shift+enter, not just with enter.

claytorm wrote:
> Hi,
>
> I want to apply TRIMEAN to a filtered data set. TRIMEAN ignores a set
> percentage of outliers. I am currently using AVERAGE embedded in
> SUBTOTAL, but there is no option to embed TRIMEAN within SUBTOTAL. Any
> suggestions on how I could find a solution to this?
>
> Bertie
>
>