Average without min and max

[jennifer]

How can I calculate an average of a list of numbers once the minimum and maximum numbers have been removed?

 

[Hui]

Jennifer

Firstly, Welcome to the Chandoo.org Forums

Try the Averageifs() function
=AVERAGEIFS(A1:A10,A1:A10,"<>"&MAX(A1:A10),A1:A10,"<>"&MIN(A1:A10))
Adjust range to suit

 

[jennifer]

Jennifer

Firstly, Welcome to the Chandoo.org Forums

Try the Averageifs() function
=AVERAGEIFS(A1:A10,A1:A10,"<>"&MAX(A1:A10),A1:A10,"<>"&MIN(A1:A10))
Adjust range to suit

Wonderful....thank you!

 

[iferror]

alernatively
=(sum(range)-max(range)-min(range))/(count(range)-2)

 

[SirJB7]

@iferror
Hi!
I think that the idea of the OP is to exclude extreme values, so your formula doesn't work as it doesn't exclude all repetitions of the max and min values, while @Hui's one does.
With the data 1,1,1,1,1,10,20,100,100,100, I'd expect to get a 15 which doesn't happen with your formula.
However let @jennifer define what suits better her requirements.
Regards!

 

[iferror]

sure i overlooked that...i happened to have had a similar problem recently but in my case my formula was correct

 

[Colin Legg]

@iferror

Just to add, to replicate your formula you could use the built-in TRIMMEAN() function. ie.
=TRIMMEAN(A1:A10,2/COUNT(A1:A10))

 

[iferror]

Thanks @Colin Legg i didn't know that... Got to search the web for all new functions in the last few version of excel, it's about time (even though i still prefer array entered count(if on the modern countifs )