• Built-In Functions (Excel 2000)

    Author
    Topic
    #357423

    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,

    Viewing 0 reply threads
    Author
    Replies
    • #530981

      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.

      • #531116

        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

        • #531163

          blushSorry, 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!

    Viewing 0 reply threads
    Reply To: Built-In Functions (Excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: