max and min

[Guest]

I want that someone tell me how do I write the formula to do this: From a
range of numbers in a colunm, I pretend to calculate de MEDIA, excluding the
two big and the two smaller numbers. Is it possible?

 

[Paul Corrado]

If by "MEDIA" you mean the number in the middle (MEDIAN) then there is not
need to eliminate the largest and smallest value. The answer is the same

If you just wish to have the AVERAGE (sum of all data divided by count of
the data) then, with your data in A1:A5 the formula would be

=(SUM(A1:A5)-MAX(A1:A5)-MIN(A1:A5))/(COUNT(A1:A5)-2)

 

[Niek Otten]

=(SUM(YourRange)-LARGE(YourRange,1)-LARGE(YourRange,2)-SMALL(YourRange,1)-SMALL(YourRange,2))/(COUNTA(YourRange)-4)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=MEDIAN(IF((A1:A100>SMALL(A1:A100,2))*(A1:A100<LARGE(A1:A100,2)),A1:A10
0))

Note: This does not work if you have ties in the two smallest/largest
values

 

[Niek Otten]

I assumed you meant the average. Now that I see the other answers I assume
I'm wrong.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Bernd]

He did not precisely define what he meant but I presume
you produced exactly what he wants.

It is called a "trimmed mean". See
http://www.gseis.ucla.edu/courses/ed230a2/means.html

Regards,
Bernd

 

[Ken Wright]

=TRIMMEAN(A1:A100,CEILING(4/COUNT(A1:A100),0.0001))