Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    diegov
    Guest

    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. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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