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

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

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

Try this:

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

HTH,
Elkar


"frosterrj" wrote:

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

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

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

"frosterrj" wrote in message
...
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

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

=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)

"frosterrj" wrote in message
...
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

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

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


"frosterrj" wrote:

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

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

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

ctrl+shift+enter (not just enter)


"frosterrj" wrote:

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

Average of values in row excluding the highest and lowest valu
 

Thanks! That works for me!

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

Robert

"Elkar" wrote:

Try this:

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

HTH,
Elkar


"frosterrj" wrote:

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

Average of values in row excluding the highest and lowest valu
 

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

"Ron Coderre" wrote:

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


"frosterrj" wrote:

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

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

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

"Bob Phillips" wrote in message
...
=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)

"frosterrj" wrote in message
...
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

Average of values in row excluding the highest and lowest valu
 

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


"frosterrj" wrote:

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

"Ron Coderre" wrote:

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


"frosterrj" wrote:

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

Average of values in row excluding the highest and lowest valu
 

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....

"Ron Coderre" wrote:

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


"frosterrj" wrote:

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

"Ron Coderre" wrote:

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


"frosterrj" wrote:

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

Average of values in row excluding the highest and lowest valu
 

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...

"Elkar" wrote:

Try this:

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

HTH,
Elkar


"frosterrj" wrote:

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

Average of values in row excluding the highest and lowest valu
 

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...

"Bob Phillips" wrote:

=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)

"frosterrj" wrote in message
...
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

Average of values in row excluding the highest and lowest valu
 

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" wrote:

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....

"Ron Coderre" wrote:

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


"frosterrj" wrote:

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

"Ron Coderre" wrote:

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


"frosterrj" wrote:

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

Average of values in row excluding the highest and lowest valu
 

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


"Biff" wrote:

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

"frosterrj" wrote in message
...
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

Average of values in row excluding the highest and lowest valu
 

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..

"Teethless mama" wrote:

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

ctrl+shift+enter (not just enter)


"frosterrj" wrote:

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

Average of values in row excluding the highest and lowest valu
 

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" wrote:

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..

"Teethless mama" wrote:

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

ctrl+shift+enter (not just enter)


"frosterrj" wrote:

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

Average of values in row excluding the highest and lowest valu
 

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..

"Ron Coderre" wrote:

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" wrote:

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..

"Teethless mama" wrote:

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

ctrl+shift+enter (not just enter)


"frosterrj" wrote:

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

Average of values in row excluding the highest and lowest valu
 

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

Biff

"driller" wrote in message
...
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


"Biff" wrote:

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

"frosterrj" wrote in message
...
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