How do I extract 3 middle results and average them in excel

[Guest]

A scoresheet in excel returns 5 separate scores. I need to ignore the
highest and the lowest figures and average the other three to get a result.
How do I do this. I have tried
=IF(H71<B74,B75,B76,B77, IF(H71>B74,B75,B76,B77,"0","H71")
h71 IS THE CELL CONTAINING THE SCORE WHICH SHOWS IN B73. THE OTHER FOUR
SCORES ARE ALL SHOWN IN B74, B75,B76 AND B77. i ONLY WANT THE MIDDLE THREE
SCORES TO BE AVERAGED AND PRODUCE A RESULT. HELP WOULD BE MOST APPRECIATED.

 

[Max]

One way ..

Put in H71: =1/3*(SUM(B73:B77)-MAX(B73:B77)-MIN(B73:B77))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

A scoresheet in excel returns 5 separate scores. I need to ignore the
highest and the lowest figures and average the other three to get a result.
How do I do this. I have tried
=IF(H71<B74,B75,B76,B77, IF(H71>B74,B75,B76,B77,"0","H71")
h71 IS THE CELL CONTAINING THE SCORE WHICH SHOWS IN B73. THE OTHER FOUR
SCORES ARE ALL SHOWN IN B74, B75,B76 AND B77. i ONLY WANT THE MIDDLE THREE
SCORES TO BE AVERAGED AND PRODUCE A RESULT. HELP WOULD BE MOST

APPRECIATED.

 

[Domenic]

Try...

=AVERAGE(LARGE(B73:B77,{2,3,4}))

Hope this helps!

 

[Max]

Or, even <g>: =AVERAGE(SMALL(B73:B77,{2,3,4}))
which produces the same results

 

[Peo Sjoblom]

Another way

=TRIMMEAN(B73:B77,2/COUNT(B73:B77))

--
Regards,

Peo Sjoblom

(No private emails please)