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

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

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

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

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