• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Average without min and max

jennifer

New Member
How can I calculate an average of a list of numbers once the minimum and maximum numbers have been removed?
 
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
 
@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!
 
sure i overlooked that...i happened to have had a similar problem recently but in my case my formula was correct
 
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 ;) )
 
Back
Top