Posted by Aladin Akyurek on February 11, 2002 10:23 AM

=TRIMMEAN(A1:A10,0.15)

where A1:A10 houses the values to average.

==============

Posted by Aladin Akyurek on February 11, 2002 10:55 AM

Replied too fast.

Ordinarily:

=(SUM(Range)-SUM(LARGE(Range,{1,2,3}))-SUM(SMALL(Range,{1,2,3})))/(COUNT(range)-6)

where Range is the range that houses numeric values of interest.

Note. The formula does not check if there are at least 2*N +1 numbers (N being 3 in your case).

I believe TRIMMEAN would also work if modified:

=TRIMMEAN(Range,6/COUNT(Range))

where 6 is the count of the numeric values to exclude.

Checking for 2*N+1, we get:

=IF(COUNT(Range)>6,TRIMMEAN(Range,6/COUNT(Range)),0)

============

Posted by Mark W. on February 11, 2002 11:31 AM

{=AVERAGE(IF(MID("000"&REPT(1,COUNT(A1:A10)-6)&"000",ROW(INDIRECT("1:"&COUNT(A1:A10))),1)+0,A1:A10))}

Note: This is an array formula which must be

entered using the Control+Shift+Enter key

combination. The outermost braces, {}, are not

entered by you -- they're supplied by Excel in

recognition of a properly entered array formula.

Posted by Mark W. on February 11, 2002 2:22 PM

BTW, I should have mentioned...

That this formula assumes that A1:10 has been sorted! {=AVERAGE(IF(MID("000"&REPT(1,COUNT(A1:A10)-6)&"000",ROW(INDIRECT("1:"&COUNT(A1:A10))),1)+0,A1:A10))} Note: This is an array formula which must be

Posted by Mark W. on February 11, 2002 5:03 PM

Aladin, I think you were on the right track with TRIMMEAN...

=TRIMMEAN(A:A,6/COUNT(A:A))

Posted by Aladin Akyurek on February 12, 2002 12:00 AM

Re: Aladin, I think you were on the right track with TRIMMEAN...

See the Fix below that I posted. =TRIMMEAN(A:A,6/COUNT(A:A))

Posted by Mark W. on February 12, 2002 6:57 AM

Ahh, I fixated on your SMALL/LARGE solution [nt]

Posted by Mark W. on February 12, 2002 7:11 AM

If TRUE I wouldn't return a 0 since it may be

a legitmate result of TRIMMEAN (e.g.,

{0;0;0;0;0;0;0;0;0;0} or

{-2.5;-2;-1.5;-1;0;0;1;1.5;2;2.5}). I'd just

let TRIMMEAN return #NUM! and allow the using

function handle the error. Replied too fast. Ordinarily: =(SUM(Range)-SUM(LARGE(Range,{1,2,3}))-SUM(SMALL(Range,{1,2,3})))/(COUNT(range)-6) where Range is the range that houses numeric values of interest. Note. The formula does not check if there are at least 2*N +1 numbers (N being 3 in your case). I believe TRIMMEAN would also work if modified: =TRIMMEAN(Range,6/COUNT(Range)) where 6 is the count of the numeric values to exclude. Checking for 2*N+1, we get: =IF(COUNT(Range)>6,TRIMMEAN(Range,6/COUNT(Range)),0) ============

Posted by Mark W. on February 12, 2002 7:16 AM

It appears that the TRIMMEAN help file isn't precise...

It states:

"If percent < 0 or percent > 1, TRIMMEAN returns

the #NUM! error value."

...and should state:

"If percent < 0 or percent => 1..." If TRUE I wouldn't return a 0 since it may be