How to make average function ignore MIN and MAX

L

Larry4500

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!
 
P

Paul B

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)

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

Paul B

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)

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

Bernard Liengme

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
 
B

Bernard Liengme

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
 
G

Guest

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
 
G

Guest

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
 

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

Top