Scarlet_Buckeye
Posts: 5,264
Oct 24, 2012 1:44pm
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?
Anyone have a formula for me?
FatHobbit
Posts: 8,651
Oct 24, 2012 1:49pm
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.
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.
M
MontyBrunswick
Oct 24, 2012 1:53pm
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
Posts: 8,651
Oct 24, 2012 1:55pm
If you can sort the columns, you could just average(A4:A9) and leave out the largest and smallest values.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)
M
MontyBrunswick
Oct 24, 2012 1:58pm
That would also work...looks like that's what they're doing here:FatHobbit;1303548 wrote:If you can sort the columns, you could just average(A4:A9) and leave out the largest and smallest values.
http://www.excelforum.com/excel-formulas-and-functions/573585-how-to-make-average-function-ignore-min-and-max.html
gorocks99
Posts: 10,760
Oct 24, 2012 2:06pm
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.
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
Posts: 3,722
Oct 24, 2012 2:08pm
THISgorocks99;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.
G
gut
Posts: 15,058
Oct 24, 2012 2:27pm
(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
Of course, if you have multiple instances of a min/max then it's trickier
J
jmog
Posts: 6,567
Oct 24, 2012 2:28pm
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.
=(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.
J
jmog
Posts: 6,567
Oct 24, 2012 2:30pm
Beat me too it. Nice job.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
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.
G
gut
Posts: 15,058
Oct 24, 2012 2:30pm
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
won't let me use the <> for some reason. Basically, it's a conditional average
gorocks99
Posts: 10,760
Oct 24, 2012 2:30pm
Nice, definitely use this.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.
G
gut
Posts: 15,058
Oct 24, 2012 2:32pm
He also has to be careful on the "count" function if he has non-numeric data or error values.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.
J
jmog
Posts: 6,567
Oct 24, 2012 3:15pm
Yup, hoping he realizes the issues with count function, if not he could use one of the other "count" functions available.gut;1303591 wrote:He also has to be careful on the "count" function if he has non-numeric data or error values.
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
Posts: 23,151
Oct 24, 2012 3:20pm
Nice, didn't realize there were so manu excel experts on here.
gorocks99
Posts: 10,760
Oct 24, 2012 3:20pm
Commander of Awesome;1303633 wrote:Nice, didn't realize there were so manu excel experts on here.
Commander of Awesome
Posts: 23,151
Oct 24, 2012 3:37pm
Reps
G
gut
Posts: 15,058
Oct 24, 2012 3:50pm
This is pedestrian stuff. I create my own user-functions, like a boss.Commander of Awesome;1303633 wrote:Nice, didn't realize there were so manu excel experts on here.
J
jmog
Posts: 6,567
Oct 24, 2012 7:25pm
I typically just write VBA macros for things that don't already have functionsgut;1303659 wrote:This is pedestrian stuff. I create my own user-functions, like a boss.
.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.
G
gut
Posts: 15,058
Oct 24, 2012 8:10pm
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
Posts: 5,264
Oct 24, 2012 10:37pm
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!
Thanks!
Commander of Awesome
Posts: 23,151
Oct 24, 2012 10:43pm
Don't tell my employer that!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.