Archive

Excel Help :: Using the AVERAGE function, while excluding MIN and MAX

  • Scarlet_Buckeye
    I have a table of numbers... I want to calculate the AVERAGES of each row... but I want to exclude the MIN and MAX values of each row.

    Anyone have a formula for me?
  • FatHobbit
    This is just off the top of my head, but you could have a formula for each row to calc the negative max and another to calc the negative min. Then average the rows including those fields and that should subtract them out. (I think)

    Edit - I think you will need to manually calc the average because if you have 20 fields, adding the two new columns will give you a total of 22 fields. But you really want to divide by 18 because you've excluded two of the values.
  • dlazz
    Just skip the min/max field when calculating the averages...so if you have data in A3:A6, a minimum in A7, then more data in A8:A10, just do =AVERAGE(A3:A6,A8:A10)
  • FatHobbit
    dlazz;1303547 wrote:Just skip the min/max field when calculating the averages...so if you have data in A3:A6, a minimum in A7, then more data in A8:A10, just do =AVERAGE(A3:A6,A8:A10)
    If you can sort the columns, you could just average(A4:A9) and leave out the largest and smallest values.
  • dlazz
    FatHobbit;1303548 wrote:If you can sort the columns, you could just average(A4:A9) and leave out the largest and smallest values.
    That would also work...looks like that's what they're doing here:

    http://www.excelforum.com/excel-formulas-and-functions/573585-how-to-make-average-function-ignore-min-and-max.html
  • gorocks99
    Let's say you have data in A1:A50.

    In cells B1:B50...

    =IF(OR(A1=MIN($A$1:$A$50),A1=MAX($A$1:$A$50)),"",A1)

    Then take the average of column B. That would also work although you have to have multiple formulas.
  • derek bomar
    gorocks99;1303556 wrote:Let's say you have data in A1:A50.

    In cells B1:B50...

    =IF(OR(A1=MIN($A$1:$A$50),A1=MAX($A$1:$A$50)),"",A1)

    Then take the average of column B. That would also work although you have to have multiple formulas.
    THIS
  • gut
    (sum(A:A) - min(A:A) - max(A:A))/(count(A:A)-2)

    Of course, if you have multiple instances of a min/max then it's trickier
  • jmog
    Nope. Easiest way.

    =(sum()-min()-max())/(count()-2)

    The "()" obviously include the whole data array.

    This will work no matter what sequence the numbers are in or where the min and mad is in the sequence.
  • jmog
    gut;1303578 wrote:(sum(A:A) - min(A:A) - max(A:A))/(count(A:A)-2)

    Of course, if you have multiple instances of a min/max then it's trickier
    Beat me too it. Nice job.

    If there are multiple mins or maxes then the formula will take out one but not all which is what I think the OP wants anyway.
  • gut
    or averageifs(A:A, A:A,"<max(a:a)",a:a,"><min(a:a)",a:a,">less than max(A:A)",A:A, "greater than min(A:A)")</min(a:a)",a:a,"></max(a:a)",a:a,">

    won't let me use the <> for some reason. Basically, it's a conditional average
  • gorocks99
    jmog;1303579 wrote:Nope. Easiest way.

    =(sum()-min()-max())/(count()-2)

    The "()" obviously include the whole data array.

    This will work no matter what sequence the numbers are in or where the min and mad is in the sequence.
    Nice, definitely use this.
  • gut
    jmog;1303582 wrote:Beat me too it. Nice job.

    If there are multiple mins or maxes then the formula will take out one but not all which is what I think the OP wants anyway.
    He also has to be careful on the "count" function if he has non-numeric data or error values.
  • jmog
    gut;1303591 wrote:He also has to be careful on the "count" function if he has non-numeric data or error values.
    Yup, hoping he realizes the issues with count function, if not he could use one of the other "count" functions available.

    averageif is a nice one as well, but then its harder to understand if someone is checking the function but a little more "compact" than the straight formula I used.
  • Commander of Awesome
    Nice, didn't realize there were so manu excel experts on here.
  • gorocks99
    Commander of Awesome;1303633 wrote:Nice, didn't realize there were so manu excel experts on here.
  • Commander of Awesome
    Reps
  • gut
    Commander of Awesome;1303633 wrote:Nice, didn't realize there were so manu excel experts on here.
    This is pedestrian stuff. I create my own user-functions, like a boss.
  • jmog
    gut;1303659 wrote:This is pedestrian stuff. I create my own user-functions, like a boss.
    I typically just write VBA macros for things that don't already have functions :).

    Created industrial combustion furnace heat transfer/balance models in excel/VBA.

    Stuff like that is usually done in fluid computational programs like FLUENT, or in heavy duty programming software like MathCAD, Maple, MatLAB, etc.

    I can honestly say I've never wrote my own user defined functions in excel, know how just never really had the need.
  • gut
    I don't do much VBA. Never learned. Muddle my way through by recording macros and then editing, sometimes an internet search. Can't really claim to be a master without being good in VBA. I usually laugh at people that claim to be experts or a master - don't think anyone that might actually qualify would make such a claim.
  • Scarlet_Buckeye
    Wow! Thank you so much, everyone. Really appreciate all the effort and help. I think I'm gonna roll with the suggestion from gut/jmog/gorocks99.

    Thanks!
  • Commander of Awesome
    gut;1303790 wrote:I don't do much VBA. Never learned. Muddle my way through by recording macros and then editing, sometimes an internet search. Can't really claim to be a master without being good in VBA. I usually laugh at people that claim to be experts or a master - don't think anyone that might actually qualify would make such a claim.
    Don't tell my employer that!