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

Home ▸ Archive ▸ Serious Business ▸ Excel Help :: Using the AVERAGE function, while excluding MIN and MAX
Scarlet_Buckeye's avatar

Scarlet_Buckeye

Senior Member

5,264 posts
Oct 24, 2012 1:44 PM
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?
Oct 24, 2012 1:44pm
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Oct 24, 2012 1:49 PM
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.
Oct 24, 2012 1:49pm
M

MontyBrunswick

Oct 24, 2012 1:53 PM
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)
Oct 24, 2012 1:53pm
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Oct 24, 2012 1:55 PM
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.
Oct 24, 2012 1:55pm
gorocks99's avatar

gorocks99

Senior Member

10,760 posts
Oct 24, 2012 2:06 PM
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.
Oct 24, 2012 2:06pm
derek bomar's avatar

derek bomar

Senior Member

3,722 posts
Oct 24, 2012 2:08 PM
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
Oct 24, 2012 2:08pm
G

gut

Senior Member

15,058 posts
Oct 24, 2012 2:27 PM
(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
Oct 24, 2012 2:27pm
J

jmog

Senior Member

6,567 posts
Oct 24, 2012 2:28 PM
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.
Oct 24, 2012 2:28pm
J

jmog

Senior Member

6,567 posts
Oct 24, 2012 2:30 PM
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.
Oct 24, 2012 2:30pm
G

gut

Senior Member

15,058 posts
Oct 24, 2012 2:30 PM
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
Oct 24, 2012 2:30pm
gorocks99's avatar

gorocks99

Senior Member

10,760 posts
Oct 24, 2012 2:30 PM
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.
Oct 24, 2012 2:30pm
G

gut

Senior Member

15,058 posts
Oct 24, 2012 2:32 PM
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.
Oct 24, 2012 2:32pm
J

jmog

Senior Member

6,567 posts
Oct 24, 2012 3:15 PM
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.
Oct 24, 2012 3:15pm
Commander of Awesome's avatar

Commander of Awesome

Senior Pwner

23,151 posts
Oct 24, 2012 3:20 PM
Nice, didn't realize there were so manu excel experts on here.
Oct 24, 2012 3:20pm
gorocks99's avatar

gorocks99

Senior Member

10,760 posts
Oct 24, 2012 3:20 PM
Commander of Awesome;1303633 wrote:Nice, didn't realize there were so manu excel experts on here.
Oct 24, 2012 3:20pm
Commander of Awesome's avatar

Commander of Awesome

Senior Pwner

23,151 posts
Oct 24, 2012 3:37 PM
Reps
Oct 24, 2012 3:37pm
G

gut

Senior Member

15,058 posts
Oct 24, 2012 3:50 PM
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.
Oct 24, 2012 3:50pm
J

jmog

Senior Member

6,567 posts
Oct 24, 2012 7:25 PM
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.
Oct 24, 2012 7:25pm
G

gut

Senior Member

15,058 posts
Oct 24, 2012 8:10 PM
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.
Oct 24, 2012 8:10pm
Scarlet_Buckeye's avatar

Scarlet_Buckeye

Senior Member

5,264 posts
Oct 24, 2012 10:37 PM
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!
Oct 24, 2012 10:37pm
Commander of Awesome's avatar

Commander of Awesome

Senior Pwner

23,151 posts
Oct 24, 2012 10:43 PM
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!
Oct 24, 2012 10:43pm