How can I take average of middle 3 values?

Posted by Steve on January 08, 2002 6:51 AM

Let's say that I have 5 numbers, A1,A2,A3,A4,A5

I'd like to be able to make excel take the average of this set, excluding the highest and lowest value. I know I could sort them, and take average of A2,A3,and A4, but I'd like to avoid doing that. Thanks for any help in advance!!

Steve

Posted by Scott on January 08, 2002 7:04 AM

Here's one way:

=(SUM(A1:A5)-(MIN(A1:A5)+MAX(A1:A5)))/(COUNT(A1:A5)-2)

Posted by Aladin Akyurek on January 08, 2002 7:13 AM

Steve --

Try:

=(SUM(A1:A5)-(MAX(A1:A5)+MIN(A1:A5)))/MAX(1;(COUNT(A1:A5)-2))

Aladin

===========

Posted by Mark W. on January 08, 2002 7:16 AM

=AVERAGE(LARGE(A1:A5,{2,3,4})) (nt)