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
