ignore MAX and MIN values in a set to calculate average

[Dave F]

I have a set of numbers, say in range A1:A10. I've determined that
the maximum and minimum values in this set are outliers and so want to
ignore them when calculating the average.

I know I can ignore either the max OR the min by using one of these
array formulas: =AVERAGE(IF(A1:A10<MAX(A1:A10),A1:A10)) or
=AVERAGE(IF(A1:A10>MIN(A1:A10),A1:A10))

How could I combine exclusion of both MAX and MIN?

Dave

 

[T. Valko]

Try this:

=TRIMMEAN(A1:A10,2/COUNT(A1:A10))

 

[Ron Coderre]

See the TRIMMEAN function in Excel Help.

Then try something like this:

=TRIMMEAN(A1:A10,2/COUNT(A1:A10))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[JE McGimpsey]

One way:

=TRIMMEAN(A1:A10,0.2)

 

[David Biddulph]

=TRIMMEAN(A1:A10,20%) will eliminate the outlying 20% from your set of 10
data points, so it will lose 1 point from each end of the range.

 

[JE McGimpsey]

And if you want to exclude multiple values of MAX and MIN
(array-entered):

=AVERAGE(IF(A1:A10<>MAX(A1:A10),IF(A1:A10<>MIN(A1:A10),A1:A10)))