Average of values in row excluding the highest and lowest value?

[frosterrj]

What would be the best way to use the avg() function (or any other
appropriate function) on a column or row while excluding the highest and
lowest value?

is this possible in one cell, or does it have to be split up using min() and
max() somewhere to exclude the value?

Thanks,
Robert

 

[Elkar]

Try this:

=(SUM(A1:A100)-MIN(A1:A100)-MAX(A1:A100))/(COUNT(A1:A100)-2)

HTH,
Elkar

 

[Biff]

One way:

=(SUM(A1:E1)-MIN(A1:E1)-MAX(A1:E1))/(COUNT(A1:E1)-2)

You might want to make sure there are enough values to average:

=IF(COUNT(A1:E1)<3,0,(SUM(A1:E1)-MIN(A1:E1)-MAX(A1:E1))/(COUNT(A1:E1)-2))

Biff

 

[Bob Phillips]

=AVERAGE(IF(((A1:A10<>MAX(A1:A10))*(A1:A10<>MIN(A1:A10))),A1:A10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Ron Coderre]

Try this:

With
A1:I1 containing numbers

This formula trims the high and low from the range and calcs the average
J1: =TRIMMEAN(A1:I1,2/COUNT(A1:I1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Teethless mama]

=AVERAGE(IF((A1:A6<>MAX(A1:A6))*(A1:A6<>MIN(A1:A6)),A1:A6))

ctrl+shift+enter (not just enter)

 

[frosterrj]

Thanks! That works for me!

didn't even think about doing the average the long way.....

Robert

 

[frosterrj]

wow, there's a function I've never used before! Can't really wrap my mind
around how 2/count() removed the highest and lowest values though. I get the
correct number anyway. Help for the function describes that argument as the
% to exclude from the calc. Would have never guessed it could mean the
actual top and bottom values.

Thanks for the help.

Robert

 

[Biff]

Caveat:

If there are multiple instances of MIN and/or MAX this formula EXCLUDES ALL
instances. Also calculates empty cells as 0 unless the MIN or MAX = 0.

Biff

 

[Ron Coderre]

By way of explanation....

The 2/COUNT() calculates the percentage of the numeric values that 2 items
represents. For example, if there are 10 numbers, then 2 represents 20% of
10....which the TRIMMEAN() function culls evenly from the high and low ends
of the values.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP

 

[driller]

excuse me Ron C,. i just want to learn here,
A1:I1 (9 cells non-blank)
i type numbers :1,1,3,3,3,3,3,3,6,6
from the formula the result at J1 = 3.14
please clarify for us....

 

[driller]

excuse me Elkar, i just want to learn here,
from formula
=(SUM(A1:A10)-MIN(A1:A10)-MAX(A1:A10))/(COUNT(A1:A10)-2)
I type on A1:A10 (10 cells non-blank)
1,1,3,3,3,3,3,3,6,6
the result is 3.125
please clarify...

 

[driller]

excuse me Bob Phillips, just want to learn here, i hope no blacklisting,
from formula
=AVERAGE(IF(((A1:A10<>MAX(A1:A10))*(A1:A10<>MIN(A1:A10))),A1:A10))
from A1:A10 (10 cells non-blank all positive)
i type
1,1,3,3,3,3,3,3,6,6
the result is #VALUE!
please clarify...

 

[Ron Coderre]

In your example:
1,1,3,3,3,3,3,6,6

J1: =TRIMMEAN(A1:I1,2/COUNT(A1:I1)) returns the average of:
1,3,3,3,3,3,6
(discarding single instances of the largest and smallest values)

***********
Regards,
Ron

XL2002, WinXP

 

[driller]

excuse me biff, i just want to learn here without backgroud checking,
i test the ONE WAY of your 2 formulas
A1:E1 data 1,1,3,3,6
from
=(SUM(A1:E1)-MIN(A1:E1)-MAX(A1:E1))/(COUNT(A1:E1)-2)
and
=IF(COUNT(A1:E1)<3,0,(SUM(A1:E1)-MIN(A1:E1)-MAX(A1:E1))/(COUNT(A1:E1)-2))
both have same result =2.333

please clarify

 

[driller]

excuse mama, just want to learn here...
from formula
=AVERAGE(IF((A1:A6<>MAX(A1:A6))*(A1:A6<>MIN(A1:A6)),A1:A6))
it works perfect yet,,
when i type
on A1:A6 ...1,1,3,leave as blank,3,6,6
the result is 2..
pls. clarify..

 

[Ron Coderre]

TM...please excuse my interjection..

Driller....please allow me to offer you some posting suggestions.
You're demonstrating pattern of challenging every post that interests you...
Even the responses that don't work. It's usually worth the effort to figure
out why certain methods work or don't work for yourself. Many times the
answers you are looking for are prominent in the Excel Help files. While I
applaud your enthusiasm, you certainly don't want to develop a reputation for
hijacking topics from the people who originally posted the questions. Also,
please bear in mind that the people who post responses are merely guides, not
slaves. Our goal is to head you in the right direction, not to do your work
for you.

***********
Regards,
Ron

XL2002, WinXP

 

[driller]

thanks and no problem, I just want to avoid double posting...I hope frosterrj
will not be offended since i am not suppose to check the answer neither
interested on a check mark,, really i just want to learn here...
thanks for discussing..

 

[Biff]

The second formula makes sure there are at least 3 values to calculate. Try
the formulas on these numbers: 1;1.

Biff