The data does not need to be sorted.

There's only one way to tell if it works!

A1 = 55
A2 = 27
A3 = 99
A4 = 10
A5 = 50

Average excluding 99 and 10:

=TRIMMEAN(A1:A5,2/COUNT(A1:A5))


--
Biff
Microsoft Excel MVP


"Paul" wrote in message
...
Again, sorry for my late response (notification email got buried). As I
mentioned above, I think this requires that my list be sorted from max to
min
or vice versa. It is however in date order so I'm not sure this function
would work in my situation.

Thanks.

"T. Valko" wrote:

I guess I should've include a method that excludes *all* instances of min
and max.

Array entered** :

=AVERAGE(IF((C3:C26MIN(C3:C26))*(C3:C26<MAX(C3:C2 6)),C3:C26))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
If you only want to exclude one instance each of the max and the min:

=TRIMMEAN(C3:C26,2/COUNT(C3:C26))

For example: 3,3,4,5,6,7,7

The above formula will exclude one 3 and one 7.

--
Biff
Microsoft Excel MVP


"Paul" wrote in message
...
I have a column of values that represent job completion times (i.e.
8:45,
9:26, etc). I am trying to calculate the average completion time
excluding
the best and worst times (max and min values). Not sure now where I
got
this
original formula but it works:

=AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,""))))

The problem seems to be in how the formula gets captured in the cell.
I
can
see that for those cells where it returns a valid value, when I click
on
the
cell (but am not editing yet), the formula bar shows the formula
bounded
by
braces { }. When you edit, they are not visible.

For those cells where I do not see the braces, the formula returns
#VALUE!.
However, even in these instances, if you edit the cell and use the
formula
button (fx), the dialogue box returns the correct final result.

Is the
(a) some special way to enter the braces (if I type in the { } myself
it
makes the cell text and doesn't evaluate the formula)?
(b) a better formula?

Thanks.