ignore MAX and MIN values in a set to calculate average
 

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:A10MIN(A1:A10),A1:A10))

How could I combine exclusion of both MAX and MIN?

Dave

ignore MAX and MIN values in a set to calculate average
 

Try this:

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

--
Biff
Microsoft Excel MVP


"Dave F" wrote in message
ups.com...
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:A10MIN(A1:A10),A1:A10))

How could I combine exclusion of both MAX and MIN?

Dave

ignore MAX and MIN values in a set to calculate average
 

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)

"Dave F" wrote in message
ups.com...
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:A10MIN(A1:A10),A1:A10))

How could I combine exclusion of both MAX and MIN?

Dave

ignore MAX and MIN values in a set to calculate average
 

One way:

=TRIMMEAN(A1:A10,0.2)


In article . com,
Dave F wrote:

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:A10MIN(A1:A10),A1:A10))

How could I combine exclusion of both MAX and MIN?

Dave

ignore MAX and MIN values in a set to calculate average
 

=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.
--
David Biddulph

"Dave F" wrote in message
ups.com...
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:A10MIN(A1:A10),A1:A10))

How could I combine exclusion of both MAX and MIN?

Dave

ignore MAX and MIN values in a set to calculate average
 

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)))


In article ,
JE McGimpsey wrote:

One way:

=TRIMMEAN(A1:A10,0.2)


In article . com,
Dave F wrote:

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:A10MIN(A1:A10),A1:A10))

How could I combine exclusion of both MAX and MIN?

Dave