Average, excluding min/max

[brett.kaplan]

Hi,

I'm looking to create a formula where if I have a list of anywhere
between 3 and 10 numbers, I automatically exclude the max and the min,
and average the remaining. However, if I have only have 1, I take
that as is, and if I have 2, I average those 2.

For instance, the results should be the below:

List: 1, 7, 7, 7, 7, 7, 7, 7, 7, 10
Result: 7

List: 1, 7, 10
Result: 7

List: 7, 10
Result: 8.5

List: 1
Result: 1


I'm currently using several IF statements and averaging a bunch of
LARGE formulas within the IF statement, but is there a cleaner way to
do this?

Thanks!
Brett

 

[Bob Phillips]

=IF(COUNT(A1:M1)<3,AVERAGE(A1:M1),AVERAGE(IF((A1:M1<>MIN(A1:M1))*(A1:M1<>MAX(A1:M1))*(A1:M1<>""),A1:M1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peo Sjoblom]

Here's a simplification


=IF(COUNT(A1:M1)<3,AVERAGE(A1:M1),TRIMMEAN(A1:M1,2/COUNT(A1:M1)))

entered normally

--


Regards,


Peo Sjoblom

 

[T. Valko]

Even shorter:

=TRIMMEAN(A1:J1,(COUNT(A1:J1)>2)*2/COUNT(A1:J1))

With an error trap:

=IF(COUNT(A1:J1),TRIMMEAN(A1:J1,(COUNT(A1:J1)>2)*2/COUNT(A1:J1)),"x")