Built-In Functions (Excel 2000)

Topic

New Reply

Hi all,

I am trying to determine the average of the values in a range of cells excluding the max and the min values within that range. Is there a built-in Excel function to perform this or do I need to create a custom function?

Thanks,

Reply | Quote

Replies

Hi Michael,
If you were trying to do this to range A1:A9 then the following should work:
=(SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/(COUNT(A1:A9)-2)
Hope that helps.

Reply | Quote

This will only work if the max and the min occur once only. My recollection is that there is a function that finds the 2nd max and the 2nd min. I can’t remember it; maybe someone else can.
ruth

Reply | Quote

Sorry, I should have mentioned that, but it’s something I use so often for statistical analysis and projections where I want the average excluding the highest and lowest values (and I’m not concerned with how many of each there are), that I completely forgot! Thanks for picking it up!

Reply | Quote