# Thread: Built-In Functions (Excel 2000)

1. ## Built-In Functions (Excel 2000)

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,

2. ## Re: Built-In Functions (Excel 2000)

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.

3. ## Re: Built-In Functions (Excel 2000)

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

4. ## Re: Built-In Functions (Excel 2000)

Ok, if we are going to be picky, let's do it properly:

=(SUM(A1:A9)-MAX(A1:A9)*COUNTIF(A1:A9,MAX(A1:A9))-COUNTIF(A1:A9,MIN(A1:A9))*MIN(A1:A9))/
(9-COUNTIF(A1:A9,MIN(A1:A9))-COUNTIF(A1:A9,MAX(A1:A9)))

Cheers,

Diego V

5. ## Re: Built-In Functions (Excel 2000)

<img src=/S/blush.gif border=0 alt=blush width=15 height=15>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!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•