If there are no blanks in C3:C26, then:

=(SUM(C3:C26)-MAX(C3:C26)-MIN(C3:C26))/22

Note that the {} are signs for an array formula.

The formula above is a normal formula, not an array formula.
--
Gary''s Student - gsnu200817


"Paul" wrote:

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.