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


--
claytorm
------------------------------------------------------------------------
claytorm's Profile: http://www.excelforum.com/member.php...o&userid=11610
View this thread: http://www.excelforum.com/showthread...hreadid=398108

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" wrote in message
...

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
...
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" wrote in message
...

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