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

How could I combine exclusion of both MAX and MIN?


Ron Coderre

See the TRIMMEAN function in Excel Help.

Then try something like this:


Is that something you can work with?


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

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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question