How to make average function ignore MIN and MAX

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!

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

"Larry4500" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Larry4500" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

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


"Larry4500" wrote:

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