MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Maximum / Minimum Values


Posted by Ampleford on February 11, 2002 9:30 AM

Is it possible to take a column of figures and calculate an average removing the three highest and / or three lowest values to ensure that any "out of character" peaks or troughs are not being included in the average figure ? - Even if I can get the column to identify the three highest / lowest values I can probably work in an if of some kind after that .....

I seem to remember a similar problem a few weeks ago...


Cheers


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

Just for fun...

{=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

Checking for 2*N+1...

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