How to make average function ignore MIN and MAX

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Larry4500 - 20 Aug 2006 22:20 GMT

Is there a way to get Excel to ignore the max and min of a data range
when calculating the average? If so, is there any way to get it to
ignore the two highest and lowest values? The second question is less

important but I would really appreciate it if somebody has the answer
to my first question- it would be really useful for what I'm trying to
do. Thank you!

Reply to this Message

Paul B - 20 Aug 2006 23:13 GMT

Larry, try this

1. =(SUM(A1:A14)-SMALL(A1:A14,1)-LARGE(A1:A14,1))/(COUNT(A1:A14)-2)

2.
=(SUM(A1:A14)-SMALL(A1:A14,1)-SMALL(A1:A14,2)-LARGE(A1:A14,1)-LARGE(A1:A14,2
))/(COUNT(A1:A14)-4)

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> Is there a way to get Excel to ignore the max and min of a data range
> when calculating the average? If so, is there any way to get it to
[quoted text clipped - 3 lines]
> to my first question- it would be really useful for what I'm trying to
> do. Thank you!

Reply to this Message

Bernard Liengme - 20 Aug 2006 23:21 GMT

a) ignore Min and Max
=(SUM(myrange)-MIN(myrange)-MAX(myrange))/(COUNT(myrange)-2)
b) ignore top 2 and bottom 2
=(SUM(myrange)-(LARGE(myrange,1)+LARGE(myrange,2)+SMALL(myrange,1)+SMALL(myrange,2)))/(COUNT(myrange)-4)
This uses a named range but you could replace "myrange" by A1:A100, for
example.
In all cases be careful with parentheses: (sum - things-to-ignore) /
(count - N)
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Reply to this Message

Ron Coderre - 21 Aug 2006 00:06 GMT

Perhaps this would be a good application of the TRIMMEAN function.

For a list of numbers in A1:A10
Where you want to exclude the MAX and MIN values

B1: =TRIMMEAN(A1:A10,2/10)
or...for more flexibility
B1: =TRIMMEAN(A1:A12,2/ROWS(A1:A12))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Reply to this Message